Question This must be simple......

[NeilT]

Hi
I am tying to create a databse for our school which tracks the pupils results. I have a table (pupils_results) which stores their various results....

Name Reading Writing Maths
Neil 3a 3b 3b
Joe 3b 3a 3a
Bill 3a 3b 3c
Lucy 3c 3a 3a
so on....

I have created a query that gives me all the pupils that have scored reading 3a etc but i want to display all that data in a table....
Reading 3c 3b 3a
Lucy Joe Neil
Bill
etc..

ive tried creating a query of all my queries but get wizard cant connect record sources and ive pretty much ground to a halt so any help
or advice would be very greatly appreciated!
Thanks
Neil
Manchester

 

[Brianwarnock]

Why do you want to duplicate your data?

Brian

 

[gemma-the-husky]

the problem is that you storing the data like a spreadsheet

instead of

Name Reading Writing Maths
Neil 3a 3b 3b
Joe 3b 3a 3a
Bill 3a 3b 3c
Lucy 3c 3a 3a
so on....


you need to aim at a table that looks like this

Name Subject Mark
Neil W 3b
Meil M 3b
Joe R 3b
Joe W 3a
Joe M 3a
Bill R 3a
Bill W 3b
Bill M 3c

etc - and then things get a lot easier

 

[Brianwarnock]

But he still shouldn't store the data twice.

Brian

 

[NeilT]

Thanks for the posts!!

I dont want to store the data twice i just need to display the results in those coloumns, there are around 25-30 possible marks(levels) which are split into coloured bands and showing it like that would allow us to get a good overall spread of the class.

excuse my ignorance but how would having my data like that

Name Subject Mark
Neil W 3b
Meil M 3b
Joe R 3b
Joe W 3a
Joe M 3a
Bill R 3a
Bill W 3b
Bill M 3c

help me as i can easily make my table again like that...

Many thanks again

Neil

 

[SOS]

Once the data is all in a NORMALIZED form, then you can actually use a simple function called PARTITION to display your data in a histogram.

 

[gemma-the-husky]

when its in several different columns, if you want to find a grade like 3A - you need 3 searches, one for each column. when its vertical its only 1 column. if you have more subjects, its even worse.

you also have to resort to union queries to get the data into a useable state

its things like that that make normalisation a must!


==============
thats why your original thought that "it must be simple" is wrong - nothing is easy with unnormalised data

 

[NeilT]

Ok thanks ive read up on 1nf and tried to apply it to my data

its going to get more complicated but thought id try and get it working on a simple version.

My table now reads

Pupil_Results
Name Subject Levels
Joe Maths 3c
Neil Maths 3c
Polly Maths 3a
Sarah Maths 3b

I now need the results to be grouped by Level??

thought i was on the right lines with....

SELECT Pupil_Results.Name
FROM Pupil_Results
WHERE ((Pupil_Results.Subject)="Maths")
GROUP BY Levels,name;

but that just gives me a list of the names!!

thanks for all your help everyone

Neil

 

[georgedwilkinson]

Does this give you something closer to what you need:

SELECT name, Levels, Subject
FROM Pupil_Results
WHERE ((Pupil_Results.Subject)="Maths")
GROUP BY Levels,name;

If not, why not? In fact, what exactly do you need? Just use the query builder to get what you need...it should all be there if it's in your data set.

 

[NeilT]

I get an error on that George

cannot group on fields selected with *

im trying to get

3c 3b 3a
Joe Sarah Polly
Neil

Thanks

Neil

 

[vbaInet]

Could you show exactly what you wrote?

 

[NeilT]

george has edited his post. i still get an error though....

SELECT name, Levels, Subject
FROM Pupil_Results
WHERE ((Pupil_Results.Subject)="Maths")
GROUP BY Levels,name;

It errors saying i havent grouped by subject but have selected it.

 

[SOS]

Yes, change this line:

GROUP BY Levels,name;


to this:

GROUP BY Name, Levels, Subject;

 

[NeilT]

yea i did that but it just gives me my table i originally started with only ordered differently.

I beginning to think maybe it isnt possible to do what im asking.

thanks for the help

 

[SOS]

Can you upload a copy with some bogus data for us to work with?

 

[SOS]

You will need to run COMPACT AND REPAIR first and then ZIP it to make sure the size is okay.

 

[NeilT]

Thanks so much for all your help!!

Neil

 

[Rich]

Just a note, you shouldn't have a field called Name, it's a reserved word in Access

 

[Brianwarnock]

This displays best as a report, base a report on the query below grouping by subject. You can of course change the Aliases to what suits you.

Brian

PS Rich is of course correct on the use of name, change it to Student or even PupilName Note also avoid spaces and special characters although many people use the underscore eg Pupil_Name.

SELECT Pupil_Results.Subject, IIf([levels]="3a",[name],"") AS 3a, IIf([levels]="3b",[name],"") AS 3b, IIf([levels]="3c",[name],"") AS 3c
FROM Pupil_Results
ORDER BY Pupil_Results.Subject;

 

[NeilT]

Brian that is brilliant thanks so much!!!

Is there anyway to remove blank entries?

Thanks for everyones time and help really appreciate it!!