Question This must be simple......

[Brianwarnock]

Ok looks like I'll actually have to work on it instead of writing air code.

But first reaction is possibly not as we are working at the record level.

I'll be back, but it may be tomorrow.

Brian

 

[NeilT]

it works but would end up massive as every pupil would have a line, where as in the manual ones we do at the moment the most lines is about 8 or 9, which is children on the same level.

Thanks again

 

[Brianwarnock]

Yes I guess its back to the drawing board.

Brian

 

[Brianwarnock]

I couldn't come up with an ACCESS solution, maybe one of the real experts can, however if you would be happy doing this in Excel I have some code.

I wrote a simple query which I then exported to Excel and then ran the macro to produce the results.
I expect that the whole thing , exporting to and running the excel macro can be done from ACCESS but that is not my forte.

The access query is simply

SELECT Pupil_Results.Subject, Pupil_Results.Levels, Pupil_Results.Name
FROM Pupil_Results
ORDER BY Pupil_Results.Subject, Pupil_Results.Levels;

I have attached the spreadsheet, as Results is created each time as a new sheet you need to delete it before running the macro, and as you will see I have not attempted any formatting yet as obviously you may reject the idea of Excel. I did it for fun so wont be hurt.

Brian

 

[vbaInet]

Hey Brian, the OPs database is not normalized (AT ALL) that's probably why you couldn't reach a solution in Access. There are two tables which aren't even related.

The OP needs to do some serious normalization after which, a solution can be realised.

 

[Brianwarnock]

His table now looks like he was told to have it by Dave (aka Gemma ..), I haven't looked at his other table.

Brian

 

[vbaInet]

He would need to do more normalization if he wants to get his desired results. I think subjects like French, Spanish, Italian and English would have Reading, Writing and Speaking. So maybe a Subjects table, a Scores table, and a Levels table.