How to structure my db?

[JeredG]

I'm trying to put a solution together for our scheduling administrator and need some direction. The administrator currently keeps a list of all absesnces (approved and unapproved) in an excel file that shows the employee's name, date of absence, total hours of absence and type of absence (tardy, out sick, FMLA, etc).



What she needs is a single number showing the number of unapproved occurances the the past 365 days (rolling year). I've got the rolling year all set but I'm not sure if I need one query just for tardies and another just for out sick, etc and bring them together under a third query. Or is this something that I can build in reporter?

I've been looking into the count functions but some occurances are weighed more heavily than others which leads me to the idea of separate queries that are brought together under a single survey.

Thanks for all your help....

[June7]

Sounds like a report with Grouping & Sorting and aggregate calcs would produce the desired output. Are you linking to the spreadsheet?

[JeredG]

Are you linking to the spreadsheet?

I was intending to create a form to have all future absences be entered directly into the table. For the 'proof of concept' version I imported the database directly into access to work on it. I'm assuming that would be the preferred method instead of linking, correct?

[June7]

Yes, seems it would be best to have data in Access. You are not attempting to emulate a pay period time sheet form and data entry? This can be challenging in Access. If you want to provide some sample data will analyse.

[JeredG]

No pay period stuff, just need to roll up tardies and absences into for a rolling 365 day period into a single number. Here's where the math gets really complicated:

• Any tardies for any single agent in excess of 10 (11th or higher) equals a single occurance
• Any tardy in excess of 7 min = 1 occurance
• Three tardies less than 7 min = 1 occuance
• plus normal sick time counted

[June7]

I built a simple table to test:
Absences
EmpID...AbsenceCat...DateAbsent...HoursAbsent
1..........Tardy...........3/1/2011...... .1
1..........Tardy...........3/3/2011...... .2
2..........Tardy...........3/1/2011...... .2
2..........Sick.............3/4/2011...... 8
3..........Tardy...........3/2/2011...... .12

Query to filter and summarize data by employee. Use this query to do additional analysis of data:
SELECT Absences.EmpID, Sum(IIf([AbsenceCat]="Tardy" And [HoursAbsent]>=0.12,1,0)) AS GreaterEqual7, Sum(IIf([AbsenceCat]="Tardy" And [HoursAbsent]<0.12,1,0)) AS Less7, Sum(IIf([AbsenceCat]="Sick",[HoursAbsent],0)) AS SickTime, Sum(IIf([AbsenceCat]="Tardy",1,0)) AS TotalTardy
FROM Absences
WHERE (((Absences.DateAbsent) Between Now()-365 And Now()))
GROUP BY Absences.EmpID;

If you need more help, provide your data for analysis.