Solved

Transposing rows to columns headings in MS Access

Posted on 2012-03-14
7
173 Views
Last Modified: 2013-04-09
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
0
Comment
Question by:tmckeating
  • 4
7 Comments
 

Author Comment

by:tmckeating
ID: 37725214
I am not sure I kno what that answer means.
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 37725306
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.
0
 

Author Comment

by:tmckeating
ID: 37727020
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
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 10

Expert Comment

by:Luke Chung
ID: 37727895
0
 

Accepted Solution

by:
tmckeating earned 0 total points
ID: 37728614
I have normalised my pupil subjects table but cannot see how I can display it in a form with pupils down the left and subjects A-E as column headings. I created a crosstab query but then cannot edit the data in this format. I do not have VB experience...can you do this without VB?
0
 

Author Closing Comment

by:tmckeating
ID: 39061176
none of the others fitted and I did not have the expertise to design visual basic
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now