Return records with certain criteria

[jedder18]

Have a massive 2007 accdb. Using Windows 7
Running a massive union query.
Now have to come up with different solution in query.
Staff take tests. Staff have to pass tests with a 70% or greater score
If fail, they retake test.
Retake doesn't always occur immediately.
On failed report, I post the failed staff and which tests failed...
However, they may have retaken the test and passed.
query only pulling 1 of the failed tests on report.
I need to not show the failed score if they retook test and passed.
Currenly only asking for the failed scores on report...
need to come up with query to not show the failed score if they passed test 2nd time.

The field in query is % and criteria for report is >0.7

How do I not select the failed score field on report if they have another passing score in same table which does not need to go on report?

TIA..

Jen

 

[Peter Reid]

If you only have 2 fields, StaffID and TestScore, you can build a query grouped on StaffID showing maximum TestScores over 70%. These are the people that have passed, ie

qry_Passes
SELECT tbl_YourTable.StaffID , Max(tbl_YourTable.TestScore) AS MaxTestScore
FROM tbl_YourTable
GROUP BY tbl_YourTable.StaffID
HAVING (((Max(tbl_YourTable.TestScore))>0.7));

For the failed report, you do the same but with a maximum TestScore of 70% or less and an additional condition of where StaffID is not in the list of StaffIDs in the above query, ie

SELECT tbl_YourTable.StaffID , Max(tbl_YourTable.TestScore) AS MaxTestScore
FROM tbl_YourTable
WHERE (((tbl_YourTable.StaffID) Not In (SELECT StaffID FROM qry_Passes)))
GROUP BY tbl_YourTable.StaffID
HAVING (((Max(tbl_YourTable.TestScore))<=0.7));

 

[jedder18]

I'm trying to get out of a group query...
I would need to change all of them for the union query...
and the results are skewed

was looking into using max as a criteria for the hi value field.

by writing an expression in a new field of this existing query.

Is that an option?

 

[Peter Reid]

I don't really know how your data is stored (I've already made one assumption to offer a solution) so can't really comment on whether your suggestion is an option

 

[jedder18]

Data is stored in flat tables.
This percent field is culled from a formula.
Was looking for an expression to get the max value from the percent field without having to use a totals query as their are many fields in the query that would skew some of their results, and again, would have to change numerous queries for the union query.

need it to be something like this

IIF the max of [%] is <=0.7 use [%] else nothing (but, I don't want the null record to show on report)

The fields are all connected by a staffID

I kind of get this to work, but the staffID still shows up but a blank %.
I don't want this record to show at all.

Thanks for any and all help.

Jen