Access 2k

[kingpaul]

I'll admit, my Access skills are not that stellar. However, here's my problem, and I hope someone can help me out.

I'm in the process of building a database to record my employees' absence hours and occurrence rates over a rolling 52 week cycle. I have an employee table (SSN, fname, lname) and a rate table (employee (linked to the SSN), week-ending date, absent hours, occurrences). What I need to do is somehow total the most recent 52 entries, then divide the absences by 2080 and the occurrences by 260, which will give me the rates. Any ideas on how to accomplish that?

 

[BeerSkunk]

Use an SQL statement to grab the employee's records from the rate table and use the ORDER BY command to sort by date. Ascending or descending...whichever floats your boat.

If you sort ascending, just loop through the last 52 records summing up absences and occurrences as you go.

If you sort descending, loop through the first 52 records...

Perform your mathematical magic on the values you collected to get your rates.

//SQL Ascending
SELECT SSN, week-ending date, absent hours, occurrences FROM employee INNER JOIN rate ON employee.SSN = rate.employee WHERE employee.SSN = '123-45-6789' ORDER BY week-ending date ASC

//SQL Descending
SELECT SSN, week-ending date, absent hours, occurrences FROM employee INNER JOIN rate ON employee.SSN = rate.employee WHERE employee.SSN = '123-45-6789' ORDER BY week-ending date DESC

 

[kingpaul]

If you sort ascending, just loop through the last 52 records summing up absences and occurrences as you go.

If you sort descending, loop through the first 52 records...

< snip >

Thanks for the advice. However, and forgive me my ignorance, how do I loop through the records?

 

[BeerSkunk]

Let me change my question entirely...

Are you trying to get this information programmatically or are you trying to get it from within access...like on a report or something?

 

[kingpaul]

Let me change my question entirely...

Are you trying to get this information programmatically or are you trying to get it from within access...like on a report or something?

Within Access.

 

[Sir Whiskers]

Just off the top of my head, could you set up a parameter query to ask you what the earliest date is you want to pull, then just input the current date minus one year?

Example:

Group By: SSN
Sum: Absent Hours
Sum: Occurences
Where: WeekEnd Date >= [Enter date]

I haven't played with this, so I don't know if you'll have any problems with a paricular date yielding 51 weeks or 53 weeks. As a check, you could add a column to your query to Count WeekEnd Date - if the count was not 52, you'd have to tweak the date you input as a parameter.

Another potential problem - how do you plan to handle employees with less than 1 year's service? Their averages would be off (in their favor). A possible solution would be to Count WeekEnd Date as I mentioned above, then use that number to determine the value you divide by.

Again, I haven't actually put this together, but hopefully it will point you in the right direction.

PS - If someone knows the correct syntax for DateDiff in a query, that might simplify things. Just use DateDiff to pull all records with a WeekEnd Date within 52 weeks of the date entered in the parameter.

 

[kingpaul]

Group By: SSN
Sum: Absent Hours
Sum: Occurences
Where: WeekEnd Date >= [Enter date]

Would you believe that:

WeekEnd Date >= Date() - 365

works?

 

[Sir Whiskers]

kingpaul, I swear I tried that! Must've mistyped - oh well...