asked on
Transposing rows to columns headings in MS Access
Hi,
I am designing a database to deal with subject options for pupils ..each pupil can take 5 subjects labelled A-E. When I thought about design I was going to create Pupils / Subjects and Pupils takes subjects table and each pupil would have many rows in the pupils take subjects table with subject name and column number.
However I want to display this with one pupil per row and the subject lables A-E across top with each subject filled in.. also I need to produce a timetable and each subject gets slotted into a period in a day.
I have changed the design to have subjects A-E but every bone in my body is telling me this is BAD design.
Before I spend many more hours on this can anyone tell me if the design attached will work or if I can use my originals thoughts and still produce the two forms that I have produced in the attachment. Thought it best to ask ...cos experts exchange say just ask :-)
Any pointers /help appreciated.
Thanks.
S5S6-Options.mdb
I am designing a database to deal with subject options for pupils ..each pupil can take 5 subjects labelled A-E. When I thought about design I was going to create Pupils / Subjects and Pupils takes subjects table and each pupil would have many rows in the pupils take subjects table with subject name and column number.
However I want to display this with one pupil per row and the subject lables A-E across top with each subject filled in.. also I need to produce a timetable and each subject gets slotted into a period in a day.
I have changed the design to have subjects A-E but every bone in my body is telling me this is BAD design.
Before I spend many more hours on this can anyone tell me if the design attached will work or if I can use my originals thoughts and still produce the two forms that I have produced in the attachment. Thought it best to ask ...cos experts exchange say just ask :-)
Any pointers /help appreciated.
Thanks.
S5S6-Options.mdb
Microsoft AccessMicrosoft OfficeMicrosoft Applications
Last Comment
tmckeating
ASKER
I am not sure I kno what that answer means.
It means, your question was recategorized for a wider audience.
You are designing to a report, not designing a table and then generating a report.
Data should be normalized. Look it up in Wikipedia - third normal form.
You are on the right path with students, classes, and students assigned to classes.
I would have multiple records for the students selected classes to avoid repeating fields (see data normalization).
Will each of the classes meet every day of the week or do they have varying schedules? Would you like to include classroom, teacher assigned, etc. for additional fields?
I would use a crosstab query to pull the scheduled classes together.
I don't have time to tweak your database now, but may be able to later.
You are designing to a report, not designing a table and then generating a report.
Data should be normalized. Look it up in Wikipedia - third normal form.
You are on the right path with students, classes, and students assigned to classes.
I would have multiple records for the students selected classes to avoid repeating fields (see data normalization).
Will each of the classes meet every day of the week or do they have varying schedules? Would you like to include classroom, teacher assigned, etc. for additional fields?
I would use a crosstab query to pull the scheduled classes together.
I don't have time to tweak your database now, but may be able to later.
ASKER
I have chnaged design to normalisation rules... but with this I cannot seem to get the kind of forms i need to chnage subject data and timetable as required. Any help appreciated.
S5S6-Options---Normalised.mdb
S5S6-Options---Normalised.mdb
Here's a paper I recently wrote that might help: http://www.fmsinc.com/MicrosoftAccess/DataAnalysis/normalization/transpose.html
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
none of the others fitted and I did not have the expertise to design visual basic