Tried to do inner joins on three queries but not getting all the expected results

[aureus30]

Hi,

Recently went for a job interview in a school and had to do a test. Completely messed it up but amazingly got the job and desperate to know how to solve the test!

Was given a spreadsheet with one pupil name per row, followed by 12 subjects and the corresponding exam result. All this data was contained in 1 row. There were hundreds of rows, one for each pupil.

The task was to create 1 certificate (it was a pre-prepared certificate in Word) per child to show the pupil name plus each subject name that the pupil had achieved 'above' or 'inline' in. If the result achieved was 'below', the subject would not be printed on the certificate. If the pupil got 'below' for every subject then they would not get a certificate.

We had 30 mins to generate all the certificates.

We were told that we should import the data into Access. I've recreated the spreadsheet in access using only three subjects. I successfully did a query for each subject to extract the data I need but then tried to join the three queries using inner joins but I only get back the results for the pupils who got above/inline for all subjects.

Thanks for any help that you can provide.

[jzwp11]

Starting with the spreadsheet as was provided to you, it would be more difficult since the spreadsheet was not normalized. So the first step is to create a normalized structure and then migrate the data into that structure. To do that you will need an append query that selects the students and moves the data from the spreadsheet to the newly created table. Then you will need an append query for each subject (12 queries) to move the subjects into their respective table. Then you will need an append query to move the results for each subject/student combination (again 1 for each subject). I've attached your database with the structure and the queries for the 3 subjects you had listed.

Now to get the students and their list of subjects where something other than a "below" was obtained can be handled with 1 query. Creating the Word documents, you could probably do something with the mail merge feature, but if not you would need VBA code. I created a quick report to illustrate.