IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, firstname.lastname@example.org. Updated March 2007.
Database beginners sometimes struggle with what tables are needed, and how to relate one table to another. It's probably easiest to follow with an example.
As a school teacher, Margaret needs to track each student's name and home details, along with the subjects they have taken, and the grades achieved. To do all this in a single table, she could try making fields for:
Name Address Home Phone Subject Grade
But this structure requires her to enter the student's name and address again for every new subject! Apart from the time required for entry, can you imagine what happens when a student changes address and Margaret has to locate and update all the previous entries? She tries a different structure with only one record for each student. This requires many additional fields - something like:
Name Address Home Phone Name of Subject 1 Grade for Subject 1 Name of Subject 2 Grade for Subject 2 Name of Subject 3 ...
But how many subjects must she allow for? How much space will this waste? How does she know which column to look in to find "History 104"? How can she average grades that could be in any old column? Whenever you see this repetition of fields, the data needs to be broken down into separate tables.
The solution to her problem involves making three tables: one for students, one for subjects, and one for grades. The Students table must have a unique code for each student, so the computer doesn't get confused about two students with the same names. Margaret calls this field StudentID, so the Students table contains fields:
StudentID a unique code for each student. Surname split Surname and First Name to make searches easier. FirstName Address split Street Address, Suburb, and Postcode for the same reason. Suburb Postcode Phone
The Subjects table will have fields:
SubjectID a unique code for each subject. (Use the school's subject code) Subject full title of the subject Notes comments or a brief description of what this subject covers.
The Grades table will then have just three fields:
StudentID a code that ties this entry to a student in the Students table SubjectID a code that ties this entry to a subject in the Subjects table Grade the mark this student achieved in this subject.
After creating the three tables, Margaret needs to create a link between them. In Access 95 - 2003, she would select the Database Container window, and choosing Relationships from the Tools menu (the Edit menu in Access 1 -2.) In Access 2007, Relationships is on the Show/Hide group of the Database Tools ribbon. "Grades" will be the "related table" in relationships with both the other tables.
Now she enters all the students in the Students table, with the unique StudentID for each. Next she enters all the subjects she teaches into the Subjects table, each with a SubjectID. Then at the end of term when the marks are ready, she can enter them in the Grades table using the appropriate StudentID from the Students table and SubjectID from the Subjects table.
To help enter marks, she creates a form, using the "Form/Subform" wizard: "Subjects" is the source for the main form, and "Grades" is the source for the subform. Now with the appropriate subject in the main form, and adds each StudentID and Grade in the subform.
The grades were entered by subject, but Margaret needs to view them by student. She creates another form/subform, with the main form reading its data from the Students table, and the subform from the Grades table. Since she used StudentID when entering grades in her previous form, Access links this code to the one in the new main form, and automatically displays all the subjects and grades for the student in the main form.
Technically, the process of breaking your data down into related tables is called normalization. If you wish to read the technical rationale, follow the Access Junkie's links on database design.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Relationships between tables'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...