Duplicating Data

[Stephen]

Hi,

I have an 11 table Database all relating to "Student Information" SSN
(primary key). The rest of the tables have SSN also, but they don't have a
primary key field in any of those tables. When I create a multiple table
query with three or more tables I obtain many records if I have duplicating
SSN in the Assignment table or any other table. For example, the tables are
set up in the following manner:

Student Information Table
SSN-Primary Key (Text)
Roll Call Number – Auto Number
First Name-Text
Last Name - Text
Gender – Text
Birth Date – Date

Attendance Table
SSN – Text
Date – Date
Present –Yes/No
Absent – Yes/No
Tardy – Yes/No
Time Arrived – Text

Assignments Table
SSN – Text
Completed Assignments – Yes/ No
Type of Assignment- Text
Grade – Number

When I set up the query, I have the tables relate to the SSN. When I run
the query, I obtain duplicate data. It looks like when I have the same SSN
listed more than twice in the assignments table I get duplicate date.

I am not sure why I am getting many records. I have a join between the
tables or query. Also all the join properties are the same. I still not
sure what’s wrong.

If the table design is wong, how would i fix it. Most of the tables are set
up the same way.

Thanks in advance

 

[Duane Hookom]

Why would you want to place these three tables in the same query? If a
student has one record in the student table, 3 records in the attendance
table, and 7 records in the Assignment table, your query will display that
student with 21 records (3*7).

Normally the attendance and/or assignment tables would be displayed
individually with the student table or in separate subforms or subreports.
Subforms and subreports have their own record source/query.

 

[Stephen]

Thanks Duane,

How would i fix the query or table design to obtain the results i want. I
always use the "Student Information Table" and the "Attendane Table" in my
query. I have many tables just like the "Assignment Table", such as:
Reading Level, Writing Level, Projects, Homework, Extra Credit, and Tests.
In the query i would like to find the student (Student Iformation Table), the
date (The Attendance Table) and the grade for all his assignments (there are
many assignments) the student completed (The Assignment Table). Can you help
me fix my table design or query to obtain the results i want?

Thank you!

 

[Duane Hookom]

I don't think your table structures need fixing. Consider this small sample

tblPeople
============
Stephen

tblFavoriteColors
===================
Stephen Red
Stephen Green
Stephen Blue

tblFavoriteMVPs
=================
Stephen Marsh
Stephen Allen

I'm not sure how you would expect this to appear in a query with all three
tables. The accepted solution is to display the favorite colors and favorite
MVPs in subforms or subreports.

If Colors has a direct relationship to MVPs such as
Grandparent->Parent->Child then it would make sense to place all three
tables in a single query. The issue is that Marsh is not related to any one
color.