Duplicating Records

[Guest]

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 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.

Thanks in advance

 

[Guest]

Hi,
EVERY table needs a primary key field! This should be of type autonumber.
The primary key field should be of NO meaning to you or your users and
shouldn't event be displayed. A SSN is not a good primary key choice for many
reasons. If you really need to store this in the db then create an extra
field for it and use an autonumber for the PK as earlier mentioned.
The primary key field is only for access to work internally correct and
ensure that each record is uniquly identified. The autonumber does this
perfectly.
HTH
Good luck

 

[Guest]

Thank you for your help

Ok, how should i fix my table desgn in my tables. I have many tables, such
as: Attendance, Reading Level, Writing Level, Homework, Assignments,
Projects, Extra Credit, Quizes, and Tests. These tables all have a similiar
format, like The Assignment Table, such as: name of the subject, if it is
completed, and grade. Should i have an Autonumber field in all my tables
including Student Information and Attendance. If i do this, would the
information duplicate.

Thanks for your help!

 

[Guest]

Hi,
yes you need a primary key field in every table and this should be of type
autonumber...sample structure:

tblstudents:
StudentID PrimaryKey/Autonumber
FirstName
LastName
SSN
DOB

tblHomework
HomeworkID PrimaryKey/Autonumber
HomeworkDescription
Comments

tblAssignments
AssignmentID PrimaryKey/Autonumber
AssignmentsDescr

tblHomeWorkAssignments
HomeWorkAsignmentsID PrimaryKey/ Autonumber
StudentID ForeignKey / Number
HomeWorkID ForeignKey/ Number
AssignmentID ForeignKey/ Number
DateDone
Comments
....

This is just a sample, but should give you the idea.
HTH
Good luck

 

[Guest]

Thanks for your input but i am still not getting it. I am really dumb when
it comes to Access.

I am a new teacher and i would like to keep track of my studentss
assignments and grades. It looks like following your example i won't be able
to list many homework or class assignments for each student because of the
autonumber. Can you post or send me a three table access database that has
the fowling tables: Student Information, Attendance, and Assignments where i
will be able to list many dates (Attendance tables) and assignments and
grades (Assignment Table) for each student. I learn visually. I am sorry to
be a pain, thank you for your patience.

Stephen

 

[Guest]

Hi,
maybe it is time for you to read up on normalization. It is vital to
understand this part in database development (no matter what engine you work
with), so it won't hurt to understand and learn it. Read any or all of the
links provided in this thread:

http://www.utteraccess.com/forums/s...e=m&olderval=&oldertype=#Post752268&bodyprev=

HTH
Good luck

 

[Guest]

Thamks for the articles i will read up on it

Hi,
maybe it is time for you to read up on normalization. It is vital to
understand this part in database development (no matter what engine you work
with), so it won't hurt to understand and learn it. Read any or all of the
links provided in this thread:

http://www.utteraccess.com/forums/s...e=m&olderval=&oldertype=#Post752268&bodyprev=

HTH
Good luck