Skip to Content
Our misssion: to make the life easier for the researcher of free ebooks.

Access 2007: Basic Database Development

The Primary Key should be the table name plus “ID” (e.g., MemberID) and should be the same name in the table where it is used as a Foreign Key. (There are exceptions. In this course, the Foreign Keys LoanedToID and MemberID in tblMemberCD table both go back to the Primary Key MemberID in tblMember.) The Primary Key should always be the first field in each table, followed by any foreign key(s). Primary Keys should never be actual data—not even something unique like a Social Security number or Student ID. These values are obtained from an outside source and, while seemingly unique and reliable, could produce data entry errors.

Avoid multiple yes/no fields or fields that will be consistently left empty. Consider breaking out into a separate table or creating a Lookup Table of values to choose from. Keep items such as names (first name and last name) in separate fields. It is far easier to join those fields when needed than it is to separate a first name, last name, suffix, etc., to use in a mail merge or similar function. This also keeps them consistently in the same place. If you only have one field, you have little or no control over the order of entry.

Now is the time to review your design before you start creating your Access database. It is harder to make changes once you have data in the tables. Access is a database management system that also includes forms, queries, reports, etc. for ease of interacting with your data and extracting data from tables. Many other database systems (e.g., Microsoft SQL) require an interface be created in another program.

Access 2007: Basic Database Development