getting a Query to show and percentage for a month and a week, using Yes/No fields

[kizmissy]

I am currently trying to produce an attendance database for a small charity I work for. I have basically been teaching myself a small amount of access over the past month or so but I don't use SQL or anything just wizards and formulas etc. I am so close to being able to complete this new staff data base but i used 4 Yes/No fields for the attendance: Absent, Late, Present, Sick


and not if a person has one absent and one Present it brings up two records for that person.

I am working from countofyes queries to try and make a attendance query that will not only have one record for each person but also tell you what month the record is from
so technically one record per person per month

I realise I'm not good with describing things this is my first question on a forum so to help you help me I'm attaching a test database.

Thank For Any Help In Advance

[rpeare]

Is this intended to be a daily attendance record? If so you do not need to include a 'present' field. You can infer it from a person NOT being absent. If you have 10 employees and you record 'present' which is going to be a vast majority of your records you're going to end up with 200+ records per year per person. Now assuming you have 2 - 3 weeks of vacation and 1 - 2 weeks of sick time per year, you can reduce that number to a maximum of 25 records per person per year. Also, you do not need to keep track of different absence types in different fields. You can record it in one field and have a table that stores the absence type (Sick, Vacation, Personal, etc) make sure that field has a primary key and only store the primary key in the employee absence table.

You've also got fields on your employee table that are identical to your attendance table, if you are using that to store calculated values that's an extremely bad idea and I wouldn't do it. Calculated values should never be stored in a table.

barring all that this query will total each attendance type in your current structure by a user input date range:

SELECT AttendanceT.StaffID, StaffT.Forename, StaffT.Surname, Abs(Sum([attendancet]![Absent])) AS TotAbsent, Abs(Sum([attendancet]![Late])) AS TotLate, Abs(Sum([attendancet]![Present])) AS TotPresent, Abs(Sum([attendancet]![Sick])) AS TotSick
FROM StaffT INNER JOIN AttendanceT ON StaffT.StaffID = AttendanceT.StaffID
WHERE (((AttendanceT.AttendanceDate) Between [Enter the Starting Date] And [Enter the Ending Date]))
GROUP BY AttendanceT.StaffID, StaffT.Forename, StaffT.Surname;

Just make a query, go to the SQL code and cut and paste the code above, then run the query or go to the design view to see how it's constructed.

[alansidman]

First off, I would like to offer a couple of suggestions.
1. You don't have a form for inputting your data. It is not advisable to allow your users direct access to tables. You should use a form and then hide the tables from the users.
2. Your table is not really normalized. You should have one field that captures the type of absence. You could do this with a option control in your form (if you had one) with the selections available as radio buttons or a combo box. Then you could filter on the type of absence and do counts, etc with out much difficulty. Read here about data normalization. http://www.deeptraining.com/litwin/d...aseDesign.aspx

I concur with rpeare about not needing the "present". Assume present unless absent.

[kizmissy]

I don't understand if I'm not recording holidays or annual leave or days off how can I just assume they are present?

Also are you both suggesting it can not be done In the way fields are now? or that there would just be a lot of records?

[rpeare]

I'm not saying don't record them. I'm saying record only the days the person is ABSENT from work. You do not need to keep track of the days they are present because, by default, any day for which they are not present you will have an absentee record.

Both of us are suggesting the following

have a table for your employees, have a table for your absentee reasons, and have a reason for your employee absent dates

so if you had the following:

tblEmployees
Emp_ID Emp_FN Emp_LN ---> other employee related data
1      Mickey Mouse
2      Minnie Mouse
3      Donald Duck

tblAbsReasons
Reas_ID ReasonText
1       Sick
2       Holiday
3       Vaction

Now let's assume that mickey mouse was absent on 1/2/2012 (jan 1, 2012) as a holiday, 1/16/2012 to visit the doctor (sick) and took a vacation day on 1/30/2012.

your employee absentee log would look something like:

tblAbsLog
Abs_ID  Emp_ID  Reas_ID  Abs_Date ---> other data related to the specific absence
1       1       2        1/1/2012
2       1       1        1/16/2012
3       1       3        1/30/2012

In essence your employee will have three records for the month rather than 22.

[kizmissy]

how can i get an accurate attendance percentage if i only count the sick days and absence, im not recording the holidays or the days off and beside that the people who work here only work like 2 days a week each so the records wouldn't in fact be as many as you think for a month i dont know how what you saying will give me a definite and exact percentage

i am sorry if i seem closed minded about this but it needs to be accurate and since they don't work specific hours or days i just dont see how this could work

[alansidman]

In your field for type of Absence/Attendance, you could put in the option for present and still run your percentages. Look at this:

http://www.datapigtechnologies.com/f...centtotal.html

[rpeare]

We're just offering you alternatives and suggestions, you don't have to take them. If you feel they won't work for your situation don't use them

The structure I gave for your attendance will work with whichever path you choose. The link that alan provided will show you how to get your percentages in a query though I'd suggest you stay away from using domain (dsum, davg, dmax, dmin, etc) functions in a query as they are inefficient but it doesn't sound like your database is ever going to be huge so it may not be a problem for you, just remember to compact and repair your databse often. What I would suggest is that all your percentages could be figured out on forms or reports without having to build a query with domain functions in it.

Just keep in mind the example that alan provided (by the way, datapig is an exceptional site and a great reference so you should keep it bookmarked) is looking at ALL the data in a field and your domain function (if you use it) will have to include a criteria so instead of something like

TotalWorkdays: DCount("[Abs_Date]", "tblAbsLog", "[Emp_ID] = " & Emp_ID & " AND [Abs_Date] BETWEEN #" & [Enter Start Date] & "# AND #" & [Enter End Date] & "#")

So your entire query would be this:

SELECT tblAbsLog.Emp_ID, tblAbsLog.Reas_ID, Count(tblAbsLog.reas_ID) AS ReasonCount, [Reasoncount]/DCount("[Abs_Date]","tblAbsLog","[Emp_ID] = " & [Emp_ID] & " AND [Abs_Date] BETWEEN #" & [Enter Start Date] & "# AND #" & [Enter End Date] & "#") AS TotalWorkdays
FROM tblAbsLog
GROUP BY tblAbsLog.Emp_ID, tblAbsLog.Reas_ID;

[kizmissy]

SELECT AttendanceT.StaffID, StaffT.Forename, StaffT.Surname, Abs(Sum([attendancet]![Absent])) AS TotAbsent, Abs(Sum([attendancet]![Late])) AS TotLate, Abs(Sum([attendancet]![Present])) AS TotPresent, Abs(Sum([attendancet]![Sick])) AS TotSick
FROM StaffT INNER JOIN AttendanceT ON StaffT.StaffID = AttendanceT.StaffID
WHERE (((AttendanceT.AttendanceDate) Between [Enter the Starting Date] And [Enter the Ending Date]))
GROUP BY AttendanceT.StaffID, StaffT.Forename, StaffT.Surname;

i think this will work but i dont want to be entering a start and end date i would just like it to tell me for each month. is this possible?

[rpeare]

for each month over what span? You really want to be reporting on every month you have in your database for the rest of the time you use it? after 1 year and a half you would have quite a lengthy query.

If you don't want to be prompted for the dates take out the WHERE clause and add a field to your query using:

MonthLabel = Datepart("yyyy",[attendancedate]) & "/" & right("0" & datepart("m",[attendancedate]),2)

this would give you a label like "2012/01" or "2012/02" so you could sort the field based on themonth of the attendance. or if you're doing attendance by ear just use:

YearLabel = datepart("yyyy",[attendancedate])

[kizmissy]

thank you everyone for all your help ive managed to get things working in now shows me all my months but i took in to account what you were all saying about over time there would be too many records so i used the where statement to show only the months for the tax year.
once again thank you for being so patient and helping me do what i wanted to do