In lesson 4 Richard gave two methods of appending parents of students whom are included for mailing. The latter better method used a seperate table for the students parents where parent id associated with a student foreign key id related back to student id in students table. These two tables were then used as the basis for a mailing list query using student id from student table for linking purposes.. This makes sense in my understanding as it distinguishes entries where students and their parents might share same names as other students and parents and what if two students share same name as well as their parents - highly unlikely - but not impossible but in the first method Richard used having just the one table for students and their parents i noticed no id field used in query, not even student id - could this cause confusion when query runs, could there even be a danger of duplicate records.
Raymond Spornhauer
@Reply 5 months ago
In example 1, Rick used 1 Table which tracked the StudentID with the associated Parent in the same table.
In this example, the query was used to append a temporary table that you would use for the mailing list, which will not need the ID field as this is just a temporary table. (This is just a basic example which works with the skills you have learned so far.) This is not how most of your queries and tables will be setup in the future, but there are times it's useful to use a temporary table to simply reports.
In example 2 (the better way of setting this up), He used one Table with StudentID and a second Table with ParentID and StudentID. This is the correct way of setting this up. Keep in mind, he's using the terms 'Student' and 'Parent', but in database design, you need to understand what a 'parent record' and 'child record' is. (this does not mean family members) This is getting into Relational Data, the key to understanding the power of Access.
Hope this helps,
-Raymond
Raymond Spornhauer
@Reply 5 months ago
The problem with Example 1, is one StudentID can only have one Parent (in Ricks example)... or the the number of fields you put in that table.
In example 2, with StudentID in one table and ParentID in another table, you have have as many 'child records' as you want. And Access will maintain the relationship for you. This way you don't have tons of empty fields in your database, or not enough fields when you need more.
-Raymond
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access Expert 16.