I probably get more questions about this technique than any other, when people build their own databases. This lesson does require an understanding of primary keys, foreign keys, common fields, AutoNumbers and relationships. So if you're not sure what these terms mean, please watch those videos in the Access 2007 Essential Training and then return to this one. When you're relating two tables together, the usual method is to use the primary key of one table as the foreign key or common field in the second table. For example, let's open up our Customers table and our SalesReps table.
In the SalesReps table, each employee has his or her own ID number, a unique identifier that Access uses to make sure that the person isn't listed in the table twice. Now let's look at the ID field in Design View. Much of the time an AutoNumber data type is used for this primary key, which sequentially numbers the records as they are created, so that no two are given the same ID number. That includes this table, I'll right- click on the SalesReps tab and click Close. Let's scroll over to the right in the Customers table to see the SaleReps field.
When we get a new customer and we assign them a SalesRep, instead of re-keying all the employee's name and contact information every time, we just use their ID field. If you go to the Database tools ribbon and then click on the Relationships button, you can see that Access understands that there's a link between the SalesReps and the Customers, and that everyone SalesRep can have many Customers, as shown by the line with the one here and the infinity on the Customer side. When we later run reports, we'll use the query to bring the Customer information and the SalesRep information together into one place, using this relationship.
I'll Close this window. But where people get confused is that when they create the SalesRep field here in the Customers table, they assume that because it was an AutoNumber in the original table it has to be an AutoNumber here, but that won't work. The purpose of an AutoNumber is to automatically increment itself, that's not possible here, where I'm supposed to pick the SalesRep off of the list. So let's look at the Design View of the Customer table and click on the SalesRep field. Instead of an AutoNumber, this foreign key field must be a Number data type, and the field size should be Long Integer.
According to Access that's the only field type combination that will work to create this relationship. Setting the common field to Number and Long Integer in the foreign table will allow Access to match it to the AutoNumber in the primary key, allowing your database to merge the information properly.
Author
Released
2/8/2011- Creating new field with field templates
- Understanding errors
- Filtering data
- Aligning and distributing controls
- Customizing the interface
- Navigating quickly between records
- Speeding up data entry
- Analyzing a table for redundant data
- Creating a multi-field primary key
- Moving records with append and delete queries
- Transforming a query into a PivotTable or PivotChart
- Working with images, charts, and Smart Tags in forms
- Hiding duplicates in reports
- Formatting shortcuts
- Using macros
Skill Level Intermediate
Duration
Views
Related Courses
-
Access 2007: Queries
with Adam Wilbert2h 8m Intermediate -
Access 2010: Shortcuts
with Alicia Katz Pollock3h 43m Intermediate
-
Introduction
-
Welcome43s
-
-
1. Top Tips
-
Using input masks2m 30s
-
Four ways to filter data2m 57s
-
2. File Management
-
Opening recent files1m 49s
-
-
3. Ribbon and Quick Access Toolbar Tips
-
4. Interface Shortcuts
-
Right-clicking2m 32s
-
Using keyboard shortcuts2m 50s
-
5. Navigation Shortcuts
-
Navigating between records1m 54s
-
-
6. Data Entry and Editing Shortcuts
-
Undo and Redo tips2m 26s
-
Inserting today's date1m 14s
-
Using AutoCorrect1m 45s
-
Using concatenated fields8m 56s
-
Linking Outlook contacts2m 38s
-
-
7. Table Techniques
-
Setting data types2m 15s
-
Setting default field types2m 30s
-
Using validation rules2m 1s
-
Using flag fields2m 6s
-
Using an index1m 58s
-
Using a datasheet Totals row1m 47s
-
Filtering by selection1m 22s
-
Resizing columns and rows1m 50s
-
8. Relationship Techniques
-
Using subdatasheets1m 31s
-
Using relationship reports1m 52s
-
9. Query Techniques
-
Using wildcards5m 33s
-
Using IN instead of OR1m 34s
-
Changing captions1m 42s
-
Finding duplicate records2m 17s
-
Using make-table queries2m 23s
-
Creating an update query2m 49s
-
Using SQL statements2m 27s
-
-
10. Form Techniques
-
Creating option groups2m 35s
-
Creating tabbed form layouts4m 41s
-
Inserting charts3m 39s
-
Creating your own smart tags1m 25s
-
Hiding fields in printouts1m 32s
-
Setting tab stops4m 46s
-
11. Report Techniques
-
Creating headers and footers4m 50s
-
12. Formatting Shortcuts
-
Using the Format Painter2m 17s
-
-
13. Macro and Event Techniques
-
Using AutoExec macros1m 46s
-
14. Database Management Techniques
-
Exporting to PDF1m 50s
-
Locking files38s
-
-
Conclusion
-
Goodbye42s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Working with AutoNumbers as foreign keys