Date Criteria (1 Viewer)

[lipin]

I have an application that tracks attendance for about 400 employees. And if they have a certain # of tardies, missed days, in a last 12 month period then there are certain levels of disciplinary action that are enforced. So I have a query that goes back 365 days. Everythings great. Until someone has a Leave of Absence, for any reason, (medical, personal, military), and some may be gone 6 mos. and someone may miss a day here and there at random.

This Leave of Absence time is not supposed to count against the employees 12 mo history. My question is how do I not count those days? when I have 100+ employees that took LOA time over the last year and none were at the exact same time for the same amount of days. I need to go back 365 days for each individaul and not count the days that are coded to LOA time.

Any clue as how to handle this problem?

 

[texasalynn]

Could you put in the criteria a "not" statement. FOr example if your field has LOA entered for an employee do in the criteria line:

Not LOA*

HTH
texasalynn

 

[lipin]

tried...

The criteria I have for Date field is: >(Now()-365)
And the criteria for other fields already eliminates those coded as LOA.

But I need to exclude those days that were coded LOA from the 365 criteria for the date field somehow. I need to go back 365 days skipping those code as LOA.

So If I had 1 day of LOA it would actually need to go back 366 days and if someone else had 5 days of LOA then it would need to go back 370 days.

 

[Jon K]

Try this.

Build a Totals query to find the Sum of LOA for each Employee ID.

Then add this Totals query in the Design View of your original query. Link the table and the Totals query by the Employee ID fields. Change the criteria for the date field to:-

<Date()-(365+[QueryLOA].[SumOfLOA])

 

[lipin]

That worked great. Until I run into that person who took no LOA days. Then they are not returned in the QueryLOA so then there is no link between the tables and they are not included in the report. How do I tell the Query to include all employees even those who had no LOA days? Here is are the fields now.

Empl #
Name
Date--Count
Attendance Code--Criteria is LOA
Hrs --Criteria is 8 hrs

criteria is such that I only want to count the number of full days(8) missed and coded LOA

Thanks

 

[Jon K]

Try this:

In query Design, double-click on the line joining the table and the Totals Query. In the dialog box that pops up, choose the option that includes ALL records from the table.

Then change the criteria for the date field to:-
<Date()-(365+Nz([QueryLOA].[SumOfLOA]))

 

[lipin]

thanks

Thank You Very Much Jon K!!!

It works great! Just for future reference, what does the Nz mean/do?

 

[Jon K]

The Nz() function converts any null values to zeroes in the calculations.