Re: Duplicating Data

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.

--
Duane Hookom
MS Access MVP
--

"Stephen" <sdeserio@xxxxxxx> wrote in message
news:E27476AD-4A74-4F45-AA27-06B254C5B5B7@xxxxxxxxxxxxxxxx
> 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
>


.

Relevant Pages

  • Re: Multiple Classes
    ... Two query approach. ... First get the max level, student, a subject in one query ... SELECT SSN, Subject, Maxas MaxLevel ... Max), Teacher Name, and Period. ...
    (microsoft.public.access.queries)
  • Re: Time Schedule Query - Eric D
    ... When a student requests a Timeframe to use one of the available computers, ... eliminate that from specific timeframe from your query of what is available. ... I ran a query to delete all records in the YearlySchedule table that had ... YearlySchedule and make note as to whether or not the assignment was for a ...
    (microsoft.public.access.queries)
  • Re: Duplicating Data
    ... I'm not sure how you would expect this to appear in a query with all three ... > In the query i would like to find the student, ... > many assignments) the student completed. ... The rest of the tables have SSN also, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Generating unique records from tables
    ... SELECT SSN ... Can't be done using the query grid. ... A, B, and C that contain student records. ... but they're for different semesters. ...
    (microsoft.public.access.queries)
  • Re: Duplicating Data
    ... How would i fix the query or table design to obtain the results i want. ... In the query i would like to find the student, ... many assignments) the student completed. ... The rest of the tables have SSN also, ...
    (microsoft.public.access.tablesdbdesign)