First, Last, Min & Max Functions

[lansel]

I was reading several threads on First, Last, Min & Max Functions trying to find a way to enhance a report I have. The report I have works fine, except I have to do some manual research.

The report is an attendance report where I track the number of times an employee is absent. When the employee reaches a certain number of absences, a discipline notice is to be issued. (not my idea!!) The report I have displays the employee #, name, total incidents, the level of discipline (1, Final and Term), the date the last notice was issued and at what level. Sometimes a notice that was issued is void (level 9) and that 9 will show up as the last level issued. (I have a form that I use to find the discipline notice that is to be void and I change the level to a 9). I used the "last function" in my query to pull up the last date and level issued. The easy thing to do would be to delete the notice when it is void, but the powers that be do not want me to delete them. They want the voids to stay in the records.

Now, to save time, I would like it to pull up the last date and level issued except if it is a 9 level, then pull up the next date for that employee. I hope this is clear. In other words, I don't want the void notices to show up on my report as the last notice issued.

I tried <>9, but, of course, it just left the last date issued blank if it was a 9.

I would appreciate any suggestions.

Thanks,

Linnie

 

[lansel]

Thanks Pat for your response.

I have two joined queries. One query accumulates all the incidents by employee number. The second query pulls in the date and level of the last incident. When I used your suggestion of Max and <>9, all the employees that had a 9 were completely eliminated from the report. What I wanted was to still have the total incidents for that employee and the last date of their discipline previous to the 9. Some of the employees that didn't even have a 9 and had previous notices were deleted - I don't know what happened there!!!

Still working on it.

Thanks,

Lennie

 

[lansel]

Thanks Pat for your help. I am truly beaten.... Access wins! I have been working on this on and off for a couple of days now. I did get the 9 problem resolved, but now the rolling twelve month criteria in the other query is no longer working. All records are pulled in and totaled instead of the last 12 months. I didn't change anything on that query, just they query involving the "max" date and eliminating the level 9. All queries work fine separately. I just know it is something really simply that I'm just not getting about the join you suggested. The information from the two queries are only joined by the employee number. That is the only information they have in common.

I would welcome any suggestions.

Thanks,

Linnie

 

[lansel]

Actually, it is three queries - see attached file