Report By Quarter, different for each employee

[Rob892]

Here is what I need to do: Each employee in my department has a different hire date. I need to creata a report that shows any records for an employee by quarter, based upon their hire date. For example, An employee was hired on 09/01/01, I need a report that shows record from 09/01/01 to present with records seperated by 3 month periods, including time periods with no records. Any help on this report would be greatly appreciated! I am using Access 2000.
Rob

 

[GoDawgs]

You need to do this in the recordsource for the report. I'm thinking you can just use whatever you are using now (if the data contains employee, hire date, and date of data), then add one field:

Quarter: iif([dateofdata]<= DateAdd(&quot;m&quot;,3,[HireDate]),1,iif([dateofdata]<=DateAdd(&quot;m&quot;,6,[HireDate]),2,iif([dateofdata]<=DateAdd(&quot;m&quot;,9,[HireDate]),3,iif([dateofdata]<=DateAdd(&quot;m&quot;,12,[HireDate]),4))))

That will give each of your records a 1, 2, 3, 0r 4 for the quarter based on that employee's hire date. Then in your report you'd need to first group by Employee and then group by Quarter. Hope that helps.

Kevin

 

[Rob892]

I was afraid that the solution would look like this. I was hoping that it was possible to have Access gererate a report for a year, by quarter based on a date other that the first of the year. In any event, this problem has become a bit more interesting. What I am attempting to track are employee incentive days. The way this works is that for every 90 day period starting on an employees anniversary date that the employee has perfect attendace, they recieve 1 paid day. Unfortunately, if the employee misses work, it then begins a new 90 day period. I have a table that will hold the employees attendance records, am thinking that I will just filter this table for a 90 day period, and if no call outs are present, award 1 incentive day. If a call out is present, give no incentive day, and begin a new 90 day period. Just a bit of coding, should be easy. Any thoughts on this approach?
Rob