Compiling data from multiple records

[needsomehelp]

Hello,

I am new to Access, and have used just the very basic features. I am an advisor to many students and am working to collect data from 5-6 teachers for each student. I email teachers with a form, they complete it then send it back. My problem is that this results in 5-6 records for each student. Is there a way to combine the feedback I am given into one record?

[strive4peace]

once the data is imported into Access, you can SUM if you want to add things up ... or what do you have in mind for combining things?

[twinnyfo]

needsomehelp,

Personal opinion here (and I am probably not alone on this in the Access community), but you actually want to have a separate record for each Teacher's feedback.

The basics are like this:

You have a Table "tblStudent s" which should have a primary Key, perhaps called StudentID, which uniquely identifies each student's record. Plus all the other fields that are specific to each student (name, address, etc.)

You have a second Table "tblTeacher s" which should have a primary key, perhaps called TeacherID, which uniquely identifies each teacher's record. Plus all the other fields that are specific to each teacher (name, address, field of study, etc.)

You have a third table "tblFeedbac k" with a primary key, perhaps called FeedbackID. This table has relationships to both of the other tables, so that you have the primary key for the student and the primary key for the teacher.

Thus your third table, when populated will look something like this:

[B][U]FeedbackID   StudentID  TeacherID  FeedbackComments  FeedbackField2 ... etc.[/U][/B]
    1           1234        23     Blah Blah blah         98.5
    2           1234        45     Blah Blah blah         100.0
    3           2345        45     Blah Blah blah         87.3
    4           2345        23     Blah Blah blah         77.4
    5           3456        23     Blah Blah blah         99.1
    6           3456        56     Blah Blah blah         91.7

I hope this gets you going in the right direction.

There may be other ways of doing this, but this keeps your db in a good "normalized " state. See here for more information: Database Normalization

[needsomehelp]

Hello, thank you! I am compiling goals I will be merging onto a document that goes out to families. So I do not believe summing will work.

[needsomehelp]

Thank you for the detailed response. I will try this. Hopefully it will work the way I have in mind.