- Shopping Bag ( 0 items )
Sams Teach Yourself Microsoft Access 2000 in 21 Days is a hands-on tutorial for users who want to learn Access by working through solid examples. This book shows you how to develop solid databases from start to finish. Focusing on Access databases on the desktop, the book also addresses implementing Access in a networked or client/server environment. Key topics included relational databases and the Access 2000 architecture, designing, building, and maintaining full feature database applications, implementing Data...
Ships from: acton, MA
Usually ships in 1-2 business days
Sams Teach Yourself Microsoft Access 2000 in 21 Days is a hands-on tutorial for users who want to learn Access by working through solid examples. This book shows you how to develop solid databases from start to finish. Focusing on Access databases on the desktop, the book also addresses implementing Access in a networked or client/server environment. Key topics included relational databases and the Access 2000 architecture, designing, building, and maintaining full feature database applications, implementing Data Access Pages, working with Visual Basic for Applications and the Visual Basic Editor, and publishing Access content to the WWW or intranet.
WEEK 1. AT A GLANCE.
Day 1. Understanding Relational Database Concepts.
Data Isn't Information. The Nature of Access Data. The Theory of Database Structure. The Practice of Database Structure. Access' Mission. Hardware Requirements. Maintaining Access Databases. Summary. Q&A. Workshop.
Day 2. From Idea to Project.
The Access User Interface. Views, Opening, and Closing Objects. Global Options. The Parts of Access. Analysis of Your Project. Getting Help--The Office Assistant. Toolbars. Using the Database Wizard to Create an Application. Summary. Q&A. Workshop.
Day 3. Exploring The Data Foundations--The Table.
The Nature of a Table. Creating Your First Table. Analyzing the Wizard's Table. Linking Fields--The Heart of the Relationship. The Outcome of a Relationship. Table and Field Properties. Indexing and More on Key Fields. Sorting and Filtering in Tables. Finding Data. Summary. Q&A. Workshop.
Day 4. Using Queries For Data Retrieval.
The Query in Access. A First Query. The Simple Query Wizard. Filtering and Sorting in Queries. Query Criteria. Multitable Queries. Multitable Queries Without Existing Links. Intermediate Criteria. Summary. Q&A. Workshop.
Day 5. Creating Simple Forms For Data Access.
The Purpose of Forms in a Database. Creating a First Form. Form Design View. Form Headers and Footers. Other Form Format Properties. Tab Order. Finding, Filtering, and Sorting in Forms. Summary. Q&A. Workshop.
Day 6. Generating Basic Reports.
Reports in Access. The AutoReport Wizard. Report Wizard. The Report Design View. Grouping in Reports. Mailing Labels. Page Layout Settings. Sums, Subtotals, and Running Sums. Summary. Q&A. Workshop.
Day 7. Automating Your Access APP: A Macro Primer.
Macros and Access. Elements of a Macro. Macros and Events. Commonly Used Macro Actions. Conditional Macros. Summary. Q&A. Workshop.
WEEK 1. IN REVIEW.
WEEK 2. AT A GLANCE.
Day 8. Data Access Pages.
Access and the Web. Data Access Page Creation. The Need for Data Access Pages. The Design of Data Access Pages. Applied Data Access Pages. Summary. Q&A. Workshop.
Day 9. Refining Your Tables.
Tables and Data Integrity. Data Validation in Tables. Data Lookup in Tables. Input Masks. Using OLE Objects and Hyperlinks in Tables. Saving Tables as HTML. Summary. The Millennium Bug. Q&A. Workshop.
Day 10. Designing Customized Forms.
Making Attractive Forms. Using Colors in Forms. 3D Effects and Object Order. Formats and System Tools. Adding Artwork. Summary. Q&A. Workshop.
Day 11. Sorting Data With Queries and Joins.
General Math in Queries. Date and Time Math in Queries. Out of Order Sorting. Joins. Using Ranges in Queries. Summary. Q&A. Workshop.
Day 12. Developing Reports With Graphics and Expressions.
Examining Complex Reports. Exploring the Report Property Sheet. Adding Images to Reports. Using Expressions in Reports. Working with Subreports. Creating Reports with Multiple Columns. Summary. Q&A. Workshop.
Day 13. Introducing VBA.
Understanding VBA. Exploring Uses for VBA. Introducing the Control Wizards. Examining the Structure of VBA. Exploring the Visual Basic Editor. Introducing Language Elements. Getting Help. Summary. Q&A. Workshop.
Day 14. Understanding SQL.
What Is SQL? Understanding the Structure and Syntax of SQL. Exploring the Simple SELECT Statement. Using the WHERE Clause. Summary. Q&A. Workshop.
WEEK 2. IN REVIEW.
WEEK 3. AT A GLANCE.
Day 15. Examining Special-Use Queries.
Understanding Action Queries. Using Crosstab Queries. Understanding SQL-Specific Queries. Examining Query Properties. Creating Parameter Queries. Getting Criteria for Queries from Forms. Summary. Q&A. Workshop.
Day 16. Implementing Advanced Form Concepts.
Using Multiple-Table Forms. Constructing Forms with Subforms. Using Combo and List Boxes. Automating Forms by Using Control Wizards. Using SQL to Restrict a Form's Recordset. Creating Custom Menus and Toolbars. Summary. Q&A. Workshop.
Day 17. Developing Professional-Quality Reports.
Exploring Advanced Report Properties. Formatting Sections. Creating Special First and Last Page Headers and Footers. Developing Parameter Reports. Handling the Null (No Data) Report Event. Creating Crosstab Reports. Publishing Your Report on the Web. Exporting a Report to HTML. Summary. Q&A. Workshop.
Day 18. Examining VBA.
Reviewing the Purpose of VBA. Learning About Objects. Learning About Program Flow and Loops. Learning About Statements and Functions. Learning About Debugging and Error Handling. Summary. Q&A. Workshop.
Day 19. Working With VBA.
Using VBA to Manipulate Objects. Using VBA to Automate Data Entry. Using VBA to Work with Data Directly. Using VBA to Modify the Database. Examining the cmdExport_Click Procedure. Creating Switchboards. Summary. Q&A. Workshop.
Day 20. Understanding Access Security.
Understanding the Structure of Access Security. Setting a Database Password. Using User-Level Security. Managing Passwords. Using the Security Wizard. Closing Security Holes with MDE and ADE. Summary. Q&A. Workshop.
Day 21. Developer Considerations.
Examining Multiuser Considerations. Splitting a Database. Using External Data Sources. Accessing the Win32 API. Adding Help to Your Applications. Setting the Database Startup Properties. Summary. Q&A. Workshop.
WEEK. 3 IN REVIEW.
Appendix A. Answers.
[Figures are not included in this sample chapter]
The past two days have mired you down in theory. Today starts the real hands-onuse of Access, which, for most people, is a lot easier than abstract discussion.I think you'll also find that using Access is just plain fun. So let's get started.Today, you will learn the following:
Yesterday you saw the way a table looks in Data and Design views. Today you'lllearn how to create a table. You'll also gain an understanding of the theory behindvarious table elements and how to apply that theory to your projects.
Remember that a table is the fundamental element in a relational database. Itis the place where the system stores all data. The other objects of a database systemmanipulate or present the data, all of which is stored in these tables. The ironyof a table's position in a database system is that although it's the simplest objectto create or modify, it's also the most important in the sense that if the tablestructure is wrong, the database system can't be right.
The most important element in table design is making sure that the table's fieldsare suitable for the intended data. For example, if you define an address field asbeing able to hold only 20 characters, you will be in deep trouble when people startentering real addresses in that field. You can change much of a table's design afterthe fact, but some changes will cause ripple problems (downstream trouble) if thetable is linked to other tables, or if you have other database objects expectinga certain data structure.
The moral of the story is to plan three times and do once.
Locate and open the Tables.mdb database on your CD-ROM. Click the Tables entryin the object bar, and then open in Datasheet view (double-click on) the tblAllTypesobject. Your screen should resemble Figure 3.1.
This table has various field types (data types) and is a trivial example, butshould give you a feel for the nature of a table and the types of data it holds.
Click in the Social Security Number field. Note that Access creates a templatefor a Social Security number. That template is called an input mask. Enter a SocialSecurity number. Now click on the Last Name field and try entering the last nameGonzalez. You can't because the field size for this field has been defined too smallto be useful for a Last Name field. This is a rather obvious error, but one of thetypes you as a developer must watch out for.
Continue to enter data, observing from entries that have come before what typeof data yo u suppose the field should hold. The final field, Sound, contains a soundfile (.wav) that you can play if you have a sound-enabled computer, but don't worryabout entering new sounds in this field. Try entering apparently wrong data suchas 3/4 in the Contact Date field. The table won't accept data that's inappropriate.You won't need to (or can't) enter a number in the Friend Registration Number becausethat will auto-increment when you're finished with the record.
When you're finished experimenting, switch to Design view by clicking the viewbutton at the far left of the toolbar. Your screen should resemble Figure 3.2.
When designing your tables you must focus on the following main issues:
Use the tblAllFields object in Design view to explore the various elements ofthis elementary table's design. Pull down combo boxes; examine how the Propertiessection changes as you click different fields (having different data types). Clickthe Last Name field entry, and examine the Field Size property, noting that it'stoo small to accommodate most of the world's last names (remember the problem youhad when you tried to enter Gonzalez?).
Return to Datasheet view for this table, discarding any changes you might havemade in Design view. Place your cursor between any two columns until the cursor changesto a bar with opposite-facing arrows. Cli ck and drag, noting how you can change thevisible size of a field.
NOTE: Changing the visible size of a field will not alter its Field Size property.
Click the column head. This will highlight the column. Pressing Delete at thispoint will allow you to delete this field from your database. Click to the rightof the Social Security Number field at the record level to highlight an entire record.Your screen should resemble Figure 3.3.
TIP: Using the usual Explorer keys Ctrl and Shift, you can make multiple selections of fields or records.
Again, pressing Delete will delete any highlighted records. Keep in mind thatalthough this works for a few records, an action query is a better way to deleterecords from a live database, as discussed on Day 15, "Examining Special QueryUses."
Click any column head, keeping the mouse button pressed, and then drag the columnto the right or left. This will change the column order display, but it won't affectthe order of fields in Design view.
Finally, click between two rows on the far left of the table--in the record selectorarea. Your cursor will change to a double arrow with a bar. Drag up or down to changethe width of all records.
NOTE: All rows (records) in a table must be the same width. You can't resize rows individually.
Finally, click the Format entry in the main menu bar. Note the entries that areavailable to you for this table. Microsoft has mad e some changes to Access to makeit work somewhat like Excel for tables and queries (when feasible), so you get theExcel-like entries for hiding and freezing columns (fields).
Keep in mind that it should be a very rare or even non-existent event that userseven see a table, much less make data entries to it or edit its layout (change columnor row order or widths). Microsoft added these table facilities apparently to makeExcel experts feel more at home. An adept Access developer will have his or her dataentry done all through forms or automated by macros or VBA.
TIP: Using the same technique as record selection, you can alter (drag) the field order in Design view. This will alter the order of fields in Table (Data) view. Conversely, altering the order of fields in Table view will not alter their order in the design grid.
By now you probably have enough experience in tables and their views to have agood idea of how to create the critters, but follow along with this section becauseyou'll likely pick up a few interesting details.
There are three ways to make a table. They are
The design grid is the way almost all (maybe all) experienced Access developerscreate their tables. This grid is the only way to have the fine control of your fields'properties that you must have in any but the most elementary Access applications.
Somewhat perversely, Microsoft tries to get you to use the sample entry (shownfollowing) for tables by automat ically launching that method when you define a newblank database. This likely stems from some user focus groups, but it's truly bafflingfor most Access users.
Here are the steps to creating a table using the design grid:
2. Enter a field name in the first row of the Field Name column.
3. Press Enter or Tab to move to the Data Type field.
4. Enter a data type for this field. Access will scroll using the first letter of the data type. You can also pull down the combo box, and choose the data type from a list.
5. Alter the Field Properties section of the table design grid as needed.
6. Add a comment in the Description column if desired.
The following are the various data types and their uses:
TIP: Use the least-precise Number data type you can to preserve database size and to optimize Access' performance.
TIP: A Number data type entry can't start with a zero, so use Text for any fields (such as Social Security number) in which there exists the possibility of a leading zero or zeros.
Now that you have the preliminaries, it's time to create your first table usingthe design grid (Design view). Open the Tables database if necessary, and open thetblEmployees table in either Table view or Design view. That's the table you'll becreating in this a nd the other task lists. If you get lost, consult this table tosee where you went wrong.
2. Click the Tables entry in the object bar. Click New to start a new table. Choose Design View from the dialog box, and click OK. That will launch the design grid. You can also choose Create Table in Design View from the Database view.
3. Enter the field name Employee ID in the first row of the Field Name column. Press Enter to enter the Data Type field. Your screen should resemble Figure 3.5.
5. Click in the second row of the Field Name column and enter Last Name. Keep the Data Type as Text, but edit the Field Size property to 20.
6. Enter Field Names for first name and middle name, changing the Field Size to 15 for both, but keeping the Data Type as Text.
7. Enter the Field Name Vested and change the Data Type to Yes/No.
8. Enter a Field Name for vestment date and change the Data Type to Date/Time. Also change the Format property to Medium Date.
9. Choose File, Save or click the Save icon on the toolbar. If you're in a new database, use the name tblEmployees for a table name. If you're using the Tables database, choose another name to prevent overwriting the existing table.
10. Click the View button to switch to Table view. Your screen should resemble Figure 3.6.
NOTE: Note that Access automatically knew to use a check box for the field you designated as Yes/No.
Although there are more details, such as many other field properties you needto gain familiarity with, that's how to create a table using the Design view.
Microsoft added this facility against some objections from the database community.It's not that using this method necessarily ends up in a bad way, but it really savesnothing over using the Design view method and can lead to sloppy designs in all butthe most elementary databases. It's your call if you like using this method. To seethis table finished, open the table tblByDatasheet from the Tables database. Hereis the method in all its glory.
2. Click the New button or cho ose Create Table by entering data from the Database view. If you clicked the New button, choose Datasheet view from the dialog box and click OK. Your screen should resemble Figure 3.7.
4. Right-click on the first column header (now labeled Field1) and choose Rename from the context menu. That will place the column header in edit mode. Enter the field name Employee ID for this field.
5. Enter some name data of your own choosing (why not use your name?) for each of the next three fields, editing the field names to Last Name, First Name, and Middle Name, respectively.
6. Move to the next column, enter Yes, and edit the column header to Vested.
7. Move to the next column, enter a date such as 2/3/99, and edit the field name to Vestment Date. Your screen should resemble Figure 3.8.
9. Click the View button to switch to Design view, refusing the offer to create a primary key. Your screen should resemble Figure 3.9.
NEW TERM: A primary key is a field holding values that are unique overthe entire table. Each record in a table must have a primary key to absolutely identifyit.
Note that Access did a good job of guessing the right data types for the fieldsbased on the data you entered in the first record. You want the Text type for EmployeeID, and there's no control over the Field Size property except in Design view.
To see a limit of the Datasheet view method, switch back to Table (or Data) viewand note that the leading zeros have disappeared from the Employee ID field. Thisis because a Number data type field can't have leading zeros. From here, you canfine-tune this table to meet the exact criteria you did when you created the sametable in Design view.
It's your call if you think that this method saved time or was simpler than theall-Design view method.
The Table Wizard is somewhat misnamed. Other wizards allow you to create objectsmore or less of your own choosing. The Table Wizard only allows you to pick and choosefrom predefined tables and fields, yet it still can be a time saver. Remember, youcan use Design view to edit any fields or field properties created in any table,including those done by a wizard.
That said, take a look at the table from the Tables database called tblServiceRecords. This is the table you'll be creating using the wizard. When you're finishedreviewing the outcome of this experiment, it's time to actually do the work.
2. For something new, double-click on the Database view entry Create Table by Using Wizard. Your screen should resemble Figure 3.10.
4. Scroll down the Sample Tables list box until you find the table Service Records. Click on it to highlight it.
5. Click the single right-facing caret to move the current field from the sample table to the table you're creating. Use the double-caret button to move all fields. Similarly, you can remove a field or all fields from your table by clicking the single or double left-facing carets, respectively. For this table, include all the fields from the sample table into your table. Click the Next button to move on.
6. To maintain our naming conventions, rename the table tblService Records. Allow the wizard to set the primary key for you. Click Next.
7. The next screen is rather interesting because Access will try to establish a relationship with existing tables by examining all the primary keys. Because the other tables currently existing in Day 3 don't have anything to do with tblService Records, Access correctly guesses that no relationships should exist. Your screen should resemble Figure 3 .11.
The wizard didn't do a very good job of making a table. Although the general tableis all right, you should be able to do better and you will be able to before you'vefinished this book. Let's look at some of the design flaws in this table.
With the table in Data (or Table) view, scroll over to the Parts Replaced field.That's a normal text field. As you likely know, most repair jobs require more thanone part to be replaced, so how would a data entry person handle this field? Let'ssay a person wanted to determine the cost of this job by linking the parts to hisor her cost? There's no way to do this. The following are the two ways:
We'll correct this structure in just a moment to show the correct way to structurethis type of data.
Switch to Design view for the Service Record table. The Description field is Textdata type of 255 characters long. There is also a Problem Description field of Memodata type. There are no comments to tell you, the programmer, or the user what thedifference in these fields is, or whether the 255 characters in Problem Descriptionis sufficient.
DO use the right data type for each field.
DON'T jump to the obvious because this is often wrong. For example, use Number data type only for fields that need mathematical evaluation. Don't use Number for fields such as phone number even if you're sure nothing but numbers will ever be entered.
Equally worrisome is the AutoNumber data type for the ServiceRecordID field. Thisis all right if you want to allow Access to uniquely generate an ID for each job,but what if the service orders are pre-printed or your user wants to use a customcode for each service record?
The AutoNumber data type is useful for generating index numbers when you're surethat you won't need to edit or specify the values for this field other than the startingnumber, but it isn't ideal for all uses.
The really serious problem with tblServiceRecords as it stands is the bunchingof parts within a single field. Open the Tables databa se in the Day3 folder, if necessary;close any open objects to clear the deck, and get ready to work.
Click the Tables entry in the Object bar and locate the two tables, tblServiceIncidentand tblPartsUsed. These are the two tables you'll be linking.
The purpose of this task is to have one record for the service incident, and away to not only record all the parts for this job, but to cost it as well. The solutionis to put the service incident details in one record and the parts in many records.The reasoning will become clearer as you work through this task. You can then furtherlink up the parts-used information to cost information for those parts.
Some of the presentation in the rest of this section will be a bit beyond whereyou've gone, but try not to worry about it. Concentrate on the idea of the link,and how it makes the one-to-many relationship of having many parts to a single servicejob much clearer and more useful than lumping all the parts into one Memo field.
NOTE: Linked fields must be of the same data type. AutoNumber by increment--the data type in tblServiceIncident--uses Long Integers for incrementing, so the link field in tblPartsUsed is Number with a field size of Long Integer.
5. Click on the ServiceRecordID field in tblServiceIncident and drag your cursor to the ServiceRecordID in tblPartsUsed. Release the mouse button. When you're finished, your screen should resemble Figure 3.16.
The idea of the link field is to make sure that the items in tblPartsUsed willmatch up correctly to the right job listed in tblServiceIncident.
Currently there are two records in the tblServiceIncident table. There are severalparts in tblPartsUsed for each of these jobs. Open both tables to examine the entries.Figure 3.18 shows these two tables with their entries as of this point.
Access does include a way to add linked records to the many table quite easily,but remember it's not really a good idea to do so. Still, because it's there, it'sworth knowing about. Open the tblServiceIncident table. Note the new feature--a plussign at the extreme left of each record. Click on the plus sign. That will open upa new table-like window in which you can add parts to any job. Figure 3.19 showsthe parts window opened for job number 2.
This is one of those things that appears somewhat confusing in a screen shot.By all m eans, open tblStudentIncident and click on the plus sign yourself to seehow this facility works. Most experts believe that the best way to do data entryin a one-to-many relationship is through a form with a subform. You can get a previewof that topic by looking ahead to Day 16, "Advanced Form Concepts."
At this point, you might think that this is a lot of work compared to includingall the parts in one memo field like the original table did. Here's where you mustjump ahead a bit so you can see the reason for all this foundation work.
There is another table in the database Tables called tblPartsData. This tablecontains cost and supplier information about each part this auto repair facilitystocks or orders. If you examine that table's data or structure and tblPartsUsed,you'll note a slight violation of proper normalization because the part descriptionis repeated in each table. I left things that way to make some earlier steps clearerfor you, but in an actual application, I wouldn't have had this data duplicated.
Figure 3.20 shows a query in which the information in the two tables, tblServiceIncidentand tblPartsUsed, is linked up again. You can see that due to the link between thefields, the query is able to match up the parts used with the job where the partswere used.
The query shown in Figure 3.20 is part of the Tables database and has the nameqryBasicMatchUp. If you want to run this query, click on the Queries entry in theobject bar and double-click on its entry. Open this query in Design view to see howit is constructed.
That's fine, but you might still be wondering, "Why bother?" If youlisted all the parts in one huge Memo field, they would also be matched up with theright job.
Now it's time for the relationship trump--the matchup you can't do using the Memofield method of including many items in a single field in a table. Figure 3.21 showsanother query, this time including cost information from the table tblPartsData.This query is called qryCosting and is also part of your sample data. Open it inDesign view to see how it works if you want a peek ahead on query construction.
Yes, you could have manually entered all the cost information in the originaltable, but why bother? This way, after you've created the relational structure, Accesswill automatically look up linked information for you.
NOTE: One of the main purposes of a relational database is having the database look up related data rather than requiring that data entry personnel enter it redundantly.
If you're not convinced yet, open the query qryTotalCost in Query view by double-clickingon it. Your screen should resemble Figure 3.22.
By all means, look at the magic behind this query by switching to Design viewfor it. Although the workings of this query might not be superficially obvious, youshould have a good idea of how it's operating.
There is no reasonable way to duplicate the costing and summation data you'veseen in these past two queries by piling data into Memo fields. More importantly,having data normalized like this allows for easy lookups, faster data entry, andassured data integrity. The latter simply means that you can't enter a part for ajob that doesn't yet exist. You must create a job first, and then you can add partsto it. Otherwise, you can end up with "data orphans" in your system.
More importantly, referential integrity means you must assign a part to a job.Therefore, you're assured that you'll bill all the parts when you bill the jobs.That doesn't necessarily mean the right part is attached to the right job, but that'sanother topic.
Some of what you saw in this section is a bit too complex to go into right now(such as how that query did its totaling magic) because the information so far islikely quite a bit of a load for you at this point. Day 11 builds on the foundationlaid today.
If you remain skeptical that the referential model isn't the best for the vastmajority of data tasks, how about just suspending your disbelief for a few days?Give it a while and you'll see why it's worth the bother to create a correct relationalstructure for your database projects. After a short while, you'll see that usinga flat model would be less than satisfactory for most of what you will need to dousing Access.
Grasping the concept of linking and relationships between tables is v ital to thesuccessful use of Access and similar products. The following task will use the objectsyou're now familiar with to show how this concept works in daily use.
2. Click the plus sign to the far left of the first record in the table. This will open a window containing the linked table tblPartsUsed. Your screen should resemble Figure 3.23.
4. Click in the same area, to the left of the first record, on the sign that's now a minus. The window will close.
5. Click on the plus sign to the left of the second record to open a window for that record. Again, enter some data in the window distinct from the data you entered in step 3. Close the table.
6. Click on the Queries entry in the Database view.
7. Run the query qryBasicMatchUp by double-clicking on it. Note that the parts you entered for record 1 are now linked to that record, and those you entered for record 2 are now linked to that one. Those are the essentials of a relational database. Your screen should resemble Figure 3.24.
You've seen demonstrated only a few table properties as of now. This section willstart your tour of a few additional table properties, plus some overall table settings.
NOTE: This task uses the tblEmployees, but any table will suffice for these demonstrations.
3. Edit (or enter) Surname for the Caption property. The Caption property controls the label a field has, not only for the table, but for any objects (such as forms) derived or bound to the table. If the Caption property is blank, the field will use the field name for a Caption property. Switch to Datasheet view, saving changes when prompted. Figure 3.26 shows the results of the Caption property.
5. Use of the Format p roperty is a huge topic in detail, but the concept is simple. The idea is to alter the format or appearance of data. For example, you might want a data field to display in capital letters. To do this, enter a > for the Format property. Try that with the Last Name field. Change to Datasheet view, enter some lowercase letters in the field captioned Surname, and then leave the field. The entry will appear in capital letters. Figure 3.27 shows the use of the Format property to display a field in all caps.
NOTE: The data stored in the table with a Format property of > is in its original form--upper- or lowercase. The Format property changed only the way the data is displayed in the table.
The help system is a good reference for other format properties. Table 3.1 givesa few examples to get you going.
|Format||Enter||Displayed in Table|
|@;"Empty"||Empty (displays the word Empty until you enter data)|
The key field (primary key) is the unique identifier for a record. All tables,without exception, should have a key field identified. For example, you can't generallyuse a Last Name field as a key field because most databases will have, or at leastcan have, more than one entry for any last name. The authors of this book, Palmerand Cassel, have fairly common names. If you used last names as a key field, youcould only have one Palmer and one Cassel in the entire table--surely not a gooddesign concept. Last Name won't work for a primary key, but the authors' Social Securitynumbers are unique to them, so that would work.
If possible, use natural keys--keys that are part of the data anyway. A good exampleof this is the previously mentioned Social Security number. Everybody's is uniqueand you might have that as part of the data anyway. If you're creating an auto serviceapplication, license plate (tag) numbers should work as a primary field.
If nothing works naturally, you can resort to using Access' AutoNumber facilityto generate artificial (not part of the data) primary keys. A primary key not onlyuniquely identifies a record (you can have only one example in any table), but alsospeeds many Access operations. Don't leave the console without one.
Indexes (indices, really) order da ta within a database. An index with entriesthat can't be duplicated in that field within a table is a primary key, but you canalso have secondary indexes. Figure 3.28 shows the tblEmployees in Design view. Examinethe Field Properties for the Indexed property. The entry reads Yes (No Duplicates)for the definition of a primary key.
Click the pull-down tab for the Index property, and you'll note you can set thisproperty to No, for no index, or Yes (Duplicates OK). The latter will order the tableor at least keep a series of pointers to data for any field with that property.
Index any field you expect to search on, but except in the case of the primarykey for a table, don't make the index exclude duplicates. Doing so might end up problematicif you need two identical entries in a field. For example, if you will know thatyou'll be searching employees under last name, index the field, but don't excludeduplicates or you'll only be able to include one last name per table.
You can sort (reorder) and filter data in tables although most developers preferdoing so within queries. The next section shows how you can do these feats whilestaying within tables.
Database design purists cringe when hearing that people manipulate the views ofa table. They complain that other database objects, especially forms and queries,are for that duty. Well, cringe causing or not, people seem to like playing aroundin Table or Datasheet view rather than creating additional objects for that purpose.There's really no reason not to use tables for viewing your da ta, other than thatthe data in tables should be normalized (fragmented) into less-than-useful chunks.A query will usually reassemble data into a more useful form, but if your needs canbe met by viewing, ordering, or filtering data directly in tables, there's no reasonnot to do so.
NOTE: Edits made in a query will be reflected in the underlying tables. For example, edits made in the query shown in Figure 3.24 will be reflected in the respective tables underlying this two-table query.
To sort a table or other datasheet is database talk to order it in a determinedfashion. Ordering records alphabetically is an "alpha sort" in databaselingo. Sorting a table in Datasheet view doesn't really alter its indexes, merelythe display of the table. Here's how it's done.
2. Click the Tables entry in the object bar. Locate the Customers table and open it in Datasheet view by double-clicking on it. Your screen should resemble Figure 3.29.
4. Click the Sort Descending toolbar button. Figure 3.30 shows this button (the cursor is on it). Note the change in the table's order.
NOTE: The skills you gained today in filtering and sorting in tables are fully transferable to other objects such as queries and forms.
That's all there is to changing the sort order. To make sure you return the tableor tables to their pre-sort condition, close them and discard layout changes, andthen re-open. In this simple table, you can also sort ascending on the primary key,Customer ID, to achieve the same result.
Filtering means to filter out all but the data you let pass your criteria or criterion.Access makes this operation as simple as sorting.
2. Click the Filter by Selection button on the toolbar. Figure 3.31 shows this button and how your screen should look after clicking.
4. Click the Remove Filter button to remove any filter by selection. Click the Filter by Form button. This button is between the Filter by Selection and Apply/Remove Filter buttons. Your screen should resemble Figure 3.32.
You can use Filter by Form to save and recall filters. Here's the trick in a task.
2. Click the Filter by Form button. Choose the Contact Title field by clicking in it, and choose "Owner" from the drop-down list. Click the Save as Query button in the toolbar. Enter qryFilterDemo as a save name. Your screen should resemble Figure 3.34.
4. Click the Close button on the toolbar to close the Filter by Form screen. Close the table by choosing File, Close; discard layout changes, if so offered.
5. Open the table again. Click the Filter by Form button. Click the Load from Query button (far left). Your screen should resemble Figure 3.35.
There's one more trick with tables to cover today, but it's an easy one to grasp--findingdata within tables. Like the other skills, filtering and sorting, this skill is transferableto other database objects such as forms and queries.
2. Click in the Company Name field. Click the Find button on the toolbar (the button with the binoculars icon). Click the More button to expose all of the dialog box. Your screen should resemble Figure 3.36.
4. Pull down the other combo boxes in this dialog box to gain familiarity with other capabilities of Access' find facility. When you're finished, close everything.
Many users love the Find dialog box and use it regularly, but a few cautions areworth mentioning, as well as some tips. Here they are.
DO perform search and replace operations with caution. This can be helpful, but very dangerous. For example, if you learn that LINO-Delicateses is now LONO Delicateses and decide to search on LI and replace it with LO, allowing a Replace All will change LINO-Delicateses to LONO-Delocateses unless you tighten the search criteria. Use with care!
DO carefully check all options on the Find dialog box to make sure you know where you're looking for your hits.
DON'T apply a find to an entire table (as opposed to a single field) because this can take a long time.
DON'T apply a find to an unindexed field unless you have plenty of time; it will take longer than on an indexed field. If you plan to search regularly within a field, index it in table design view.
NOTE: You can use an action query to change table data. You'll encounter these specialized advanced queries on Day 15.
Tables store all data within a relational database such as Access. Access offersthree ways to make a table: Design view, by entry example, and by a wizard. Eachfield within a table must have a specific data type conforming to the type of datait's to hold. Each field also has an array of developer-set options called fieldproperties. The properties for each field depend upon the data type.
Primary fields act as unique identifiers for a record within a table. Althoughyou should try to use actual data as a key, using artificial data such as an incrementingAutoNumber field is much better than no key (which should never occur). By linkingfields between tables, which is the essence of a relational database, you can laterconstruct various views of data from more than one table.
Although other database objects such as forms or queries might do the job better(to some people's view), you can filter, sort, and find data within tables.
Q Why do I need a primary key for every table?
A Access enforces uniqueness for primary fields. Telling Access that a field is the primary key is the only way to be completely sure you have a unique identifier for every record in that table.
Q I want to have more than one sort for a table, but every time I change a table's field order I lose my old sort. How can I have more than one sort for a table?
A You can order or sort tables using a simple query. Each query can have its own sort order, so the answer is to use a series of queries, not sorts within tables. You can copy tables and sort them all, but that's a real waste of disk space.
Q Will the * wildcard work in Access' Find dialog box?
A Yes. For example, *c* will find any field with the letter c in it. But you really don't need this because you can also search in Any Part of Field, which covers the same territory.
The Workshop helps you solidify the skills you learned in this lesson. Answersto the quiz questions appear in Appendix A, "Answers."
2. Can the value of a primary key appear elsewhere (another field) in a record?
3. How often should identical data appear in a properly designed database?
4. If you create two tables you intend to have a one-to-many relationship, how do you create that relationship?
5. Will the letter Z appear first or last after applying a Sort (Descending) on a field containing all the letters of the alphabet?
2. Click the Filter by Form button on the toolbar. Enter a filter criterion to show only those records that begin with the letter A. Hint, use the criterion A*.
3. Apply the filter. Remove the filter.
4. If you saved the filter in the "Filtering," task click on Queries in the object bar.
5. Locate the query qr yFilterDemo. Note that the object you saved as a filter is now a query (your first query!).
6. Double-click on this object to launch the query.
7. Did the result meet your expectations?
Posted January 15, 2004
There are many descrepancies between what the book says and what actually happens in the applications. I stalled in one particular chapter and had to move on because the program absolutely would not do what the books says it will. Some of the explanations were a bit too complicated for myself to understand, and I am not a novice to computers. I finished a couple chapters not fully understanding the concepts, even after re-reading certain paragraphs. All in all, I have learned how to use Access but it took longer to go through the chapters because of the errors and vague descriptions.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted January 29, 2003
This book was recommended to me by some friends, in the fashion that the "Teach yourself _____ in 21 days" were excellent books. I was looking forward to learning this exciting new application, but as I was going along I found so many errors between what the book showed and what was on the CD that I was getting more and more frustrated as time went on. One of my biggest peeves was that the book would tell you to open the folder for today's lesson on the enclosed CD. Then you are to follow the instructions to apply what you learned. On my CD, all the exercises were completed, and there was no way for me to learn. I don't know if I just got a defective product, but the examples in the book were often wrong as well. Overall, I was very disappointed with the book and will be returning it and trying another one.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted October 11, 2001
Sams Teach Yourself Microsoft Access 2000 in 21 Days will do just about that. It goes over the fundamental elements of a database like tables, forms, querys, reports, and macros. It also goes into more advanced subjects like SQL and VBA. I didn't have a clue what these things are and now I have a pretty good understanding of what each is in Access 2000. One thing that I like about this book is the authors break each day into a chapter. Each day adds another piece of the puzzle so when you get through the end of the book you can see the big picture of how a Microsoft Access 2000 database works and how each of the elements all work together. This book has allowed me to create a database that are easy to maintain and quite functional. I must say that of all of the Microsoft Access products, Access 2000 is the most difficult to learn.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.