Attendance Query w/ Multiple Criteria

[jrosen12]

I'm trying to design a query to do something with data that our department is given regarding attendance.



I want the query to tally how many times a person has been:
<6 Minutes Tardy (field name: <6)
>6 and <60 Minutes Tardy (field name: >6<60)
UPTO (Unexecused Absence) (field name: UPTO)
NCNS (No Call/No Show) (field name: NCNS)


Then I want the query to have an expression that determines what kind of consequence is needed, if any. For example, if someone is 10 times <6 minutes Tardy, it should say "Verbal Warning." Each criteria above has about 6 different subsets (i.e. what qualifies as "verbal", "written," etc.)

I was able to successfully create the query to tally the totals and I have separate expression fields for each of the above criteria. However, the problem is that some people may have multiple instances of breaking policy where one consequence should outrank the other. For example:

Suzy was 10 times <6 minutes Tardy = Verbal Warning, but also had 1 NCNS, which is a "Final Written." Thus, I want the query to recommend "Final Written" instead of "Verbal Warning." Preferrably, I'd like to do this in a single field versus four different fields.

Is what I'm trying to do possible, or is it too complex to do in Access?

[June7]

Doing all in query might not be practical if the necessary conditional expressions to do the analysis are too complex. Might need VBA code. Build a form or report with the query as RecordSource and can have code behind the form/report.