Grouping Records

[samevans]

I have a table in my database with a many-many relationship. One field being Pupil ID, one being Subject ID, and then 12 other fields each reffering to a different type of grade i.e. Attainment, Attitude, Punctuality etc.To mail merge the table I need to have all the grades/subjects for one pupil in one record (or do i?) this is so that I can have all the grades for one pupil on one page, a report is unsuitable for me. Is there a way i can group all the records for one pupil into one or do I have to do something else?
Please help,
Sam

 

[RV]

Don't use many- to many-relationship.
It's not in accordance with normalization basics.

You're risking redundant data and/or loss of data!
Create an in between table containing the primary keys of both tables.

RV

 

[Rich@ITTC]

Hi samevans and RV

I must be honest ... the question confuses me a little ... but so does the answer given!

A single table cannot have a many to many relationship. Two tables cannot, in Access, have a many to many relationship (the only choices when relating one table to another are: One to Many and One to One). Therefore the question is confusing, to me at least, as when discussing a relationship you need to have at least two tables.

However, sometimes you do need to be able to represent a many to many relationship - e.g. in a school there are many students and they can attend many classes ... so this is not an undesireable situation to re-create in the Access database. What is required is a third 'linking' or 'bridging' table that stores the ID from the student table and the ID from the class (it may also store other data such as a start date and an end date, etc).

So, samevans, I would split the data into at least two, if not three tables.

Rich

[This message has been edited by Rich@ITTC (edited 03-08-2002).]

 

[Rich]

Design issues aside, why can't you use a Report?

 

[samevans]

I am sorry for the confusion caused by my post, when I say that my table has a many-many relationship i mean that each pupil has lots of subjects and each subject obviously has many pupils.. it is the link between these that is where the grade is stored, as each pupil has a grade for each subject. The data is normalised becase i have seperate tables that contain the data for each pupil and the data for each subject it is only the pupil id number and subject id number that are repeated. Making another table (or two!) would only repeat the same things, i have done, any other solutions would be helpful, thanks,
sam

 

[Rich@ITTC]

Hi Sam

In your Student table you shouldn't have any IDs of subjects (as these aren't elements of the entity 'Student'). Similarly, you should not have any IDs for Students in your Subjects table (as students are not part of the entity 'Subject'). Instead you need to have, as suggested above, a linking table that stores Student ID, Subject ID, Date Start, Date End, etc.

That'll do the trick!

Rich

 

[samevans]

I do have a joining table and i dont have any data about subjects in the student table or vice versa.
The problem as i tried to say above is that there are more than one record for each pupil and I need to try and combine the data for each pupil into one record i.e. column headings being pupil, subject 1 grade, subject 2 grade, subject 3 grade,
Sam