Any ideas how I could create the following report?

[russiver]

Any ideas how I could create the following report?

I have a table which contains peoples names and three additional fields – Advanced Exam (Data type yes/no), Intermediate Exam (Date type yes/no) and Novice Exam (Data type yes/no). Some people will have passed all three of these exams and others only one, the Novice.

From the above table I would like to produce a report which produces a summary of who has passed which exam, but only lists individuals under their highest achievement. So for example if an individual has passed all three exams I would only like to see his/her name listed under Advanced.

Many thanks

Russell

 

[shay]

Hi

I would base my report on a query which included following:

IIf([Advanced],"Advanced",(IIf([Intermediate],"Intermediate","Novice"))) AS HighestAchievement

shay

 

[russiver]

Thanks for the reply shay and please forgive my lack of experience with MS Access. I can see the logic of you query, but would HighestAchievement be a variable, or a calculalated field.

Russell

 

[Fear Naught]

Hi Russiver,

What I believe Shay means is "HighestAcheivement" will be a column in the query which can then be used as the data source for your report.

HTH

 

[russiver]

Think I'm with you Fear Naught. It will try this and see how I get on. I'm assuming I can simply add a column to the query and name it "HighestAchievement" without adding a field to the underlying table.

Russell

 

[shay]

Russiver

Type

HighestAchievement: IIf([Advanced],"Advanced",(IIf([Intermediate],"Intermediate","Novice")))

in a column in a select query. Select queries only select data - they don't do anything to underlying tables.

shay

 

[russiver]

Many thanks Shay, works perfectly. Exactly the output I wanted.

Russell