more than one result

[balistikb]

I have a DB that collects information of employees that has taken a test. In this table I gather information like Firstname, Lastname, employeeNumber and Grade. I need to query on the grade and run reports off of that query. I am running into a problem. I sometimes have employees that has taken the test more then once so it is thorowing my percentages off. I need if the employee has taken the test more than once show me only the pass result. I also need that if they took the test only once show me the grade whether it is pass or fail. Can someone please help me?

 

[Spellman]

Select EmployeeID, First(Firstname), First(Lastname), Max(Grade) from Employee
Group By EmployeeID


This should give the maximum grade for each employee

Mark

The key to immortality is to make a big impression in this life!!

 

[NorthNone]

I hope Mark's solution works for you, because what I came up with is far more complex:
Query1: Most recent date for each employee who has passed the test
Query2: Most recent date for each employee who has failed the test
Query3: use the above two queries and the Employee table, do a left join so that all records from the Employee table are included and only those records from Query1 that match and only those records for Query2 that match. Then add a calculated field to this query so that if there is no pass date the fail date is picked up, else use the pass date:
IIf(IsNull([emptestpassmaxdate].[Maxoftestdate]),([empTestFailedMaxDate]![MaxofTestDate]),[emptestpassmaxdate]![maxoftestdate])
I have a test database I can send if you'd like.
HTH
JeanS

 

[balistikb]

Both suggestions sound good, but I just noticed a function that might help me in the query. Has anyone used the "Last" function in a query? I ran it using that and it seems to have worked.

 

[Spellman]

Balistikb,

as far as I know the last function will only return the last record entered for that employee irrelevant of whether that employee has passed or failed this time around. It will probably work since there unlikely to sit the test more than once if they haven't already failed but if they failed a second time and got a worse grade than first time around then it would show that grade.

Just a thought!!!!!!!!!!!

Mark

The key to immortality is to make a big impression in this life!!

 

[balistikb]

It seems the first option works but if you can, can you please explain what it is doing so I know how it works? Also how does the Max work?

 

[Spellman]

What it's doing is grouping the transactions together by Employee ID so any with the same Employye ID's are grouped together. Then it taskes the First occurance of Firstname and Lastname (casue they'll be the same for all the records so you could also use last here) and the maximum value of grade and bundles them together in one record.



Mark

The key to immortality is to make a big impression in this life!!

 

[balistikb]

Ok that is understandable. But how does it choose Pass as a maximun value if the employee has fail fail pass? Don't get me wrong, it is working but I would like to learn how this is working.

 

[Spellman]

Oh so grade is a string. Well then max works alphabetically so p comes after f in the alphabet and thats why that record is being returned.

Mark

The key to immortality is to make a big impression in this life!!

 

[balistikb]

That is great, thank you very much for yet another learning experience.