Web Development

Ask something
Discussion Locked

Access Query to control a rolling Year

0 helpful
5 replies

by doc50

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
The company I work for has a policy for absenteeism which involves a rolling year. I want to be able to query a database as to which dates entered are violations currently. I also want to count these violations and use message boxes at the appropriate times to display actions required by the policy. Am I going to need 1 or 2 queries to be able to do this and what would the syntax be?

5 replies

by john_wills

I am not sure of the context of your check, but you need a WHERE with a date range between perhaps today and the same a year back; you need a count of instances and a GROUP by employee, and in the WHERE clause a specification that the count is greater than your allowed maximum. If you want a warning at entry time you should have a SQL statement within VBA using a recordset to find and inspect the count in VBA.

by bob sellman

I'll take the simple approach that the number (count) of absent dates within the year period determines the action to be taken.

I'd suggest having a (called Actions) table with three columns: AbsencesLow (integer), AbsencesHigh (integer) and Action (text or memo). AbsencesLow is the lowest number of absences that trigger the specified action and AbsencesHigh is the high end of the range of the count of absences for that action.

Assume your table with the data is called Absences and we have the columns Employee, DateAbsent. Create a query called qgrpAbsenceCount as

SELECT Employee, Count(DateAbsent) FROM Absences GROUP BY Employee WHERE DateAbsent BETWEEN Date() and DateAdd("d",Date(),-365)

(Note that you may have to tweat the DateAdd syntax listed here.)

What you do next depends upon how you want to display the action required. If you are using a form that displays data about the employee, then you can look up the action and put it into a string variable called strAction using the DLookup function:

strAction = DLookup("[Action]","[Actions]","[Employee] = '" & me.txtEmployee & "' AND " & me.txtCount BETWEEN " & [AbsencesLow] & " AND " & [AbsencesHigh])

I'm assuming that me.txtEmployee is the control on theform (or report) that contains the employee text identification (account or name).

I haven't tried to check the syntax of what I've written, but even if it's not quite right it should give you the basic idea.

by doc50

This helped me very much. The policy makes any solution cumbersome. Thank you.

This discussion is closed to new replies.