Full Version: Very complicated query calculation
monty666
Hi Everyone,
’m trying to add a table and query to my DB that will track the attendance of people to meetings. In essence, I have the employee numbers as record identifiers in the farthest left column. Each subsequent column has the date of the meeting as the column header. In each field, there is a drop down list giving the options of attendance (Present, Absent, Excused, N/A, Holidays, Sabbatical etc.). For the sake of ease lets call this table tblAttendance.
tblAttendance (please not that in this table, the semi colons represent column breaks)
Employee Num; 1Jan; 2Jan; 3Jan
Emp1; Present; Present; Present
Emp2; Present; Absent; Present
Emp3; N/A; Absent; Present
I would like to create a query called qryAttendanceRate that has a summery column that will show how many meetings were attended (a sum of all “Present” where Present = 1). I would like to create another column that shows the attendance rate either as a count or sum of Present / Total number of meetings. The logic looks a bit like this for Emp1 employee:
1-Jan = Present = 1
2 Jan = Present = 1
3 Jan = Present = 1
Total Possible Meetings 3; therefore attendance rate = 3/3 = 100%
Emp2
1-Jan = Present = 1
2 Jan = Absent = 0
3 Jan = Present = 1
Total Possible Meetings 3; therefore attendance rate = 2/3 = 66.67%
This is where it gets tricky. If a member is N/A, it means that he has not joined the company yet so……
Emp3
1-Jan = N/A = 0
2 Jan = Absent = 0
3 Jan = Present = 1
Total Possible Meeting is now = 2; therefore attendance Rate = 1/2 = 50% ( I don’t want to penalize someone who has not been to a meeting because he was not yet in the company)
Initially I had thought of creating a separate column in the query for the attendance and then another column for the possible meeting. Is essence I used 2 IIf statements.
One IIf statement in the attendance column:
Attendance: IIf([1Jan] = “Present”;1;0)
This creates a number that I can add up at the end of the row.
The second column included the following IIf statement
PossibleMtg : IIf([1Jan]="N/A";0;(IIf([1Jan]="Holiday";0;IIf([1Jan]="Sabbatical";0;1))))
This created another column with a number of possible meetings the person could attend. If they were N/A, Sabbatical or Holiday then they get 0, anything else they get 1. In the end I would get something like this (the next column after PossibleMtg would be 2Jan and then followed by the similar columns with the IIf statements until date):
qryAttendanceRate
Employee Num; 1Jan; Attendance; PossibleMtg;
Emp#1; Present; 1; 1
Emp#2; Absent; 0; 1
Emp#3; Sabbatical; 0; 0
Using this I could that Emp#1 has a 100% attendance rate (1/1), Emp#2 has a 0% attendance rate (0/1) and Emp#3 Has no data since they could not have attended the meeting since they were on Sabbatical, hence undefined.
This works for a small number of dates, however, I will likely have over 100 dates per division and several divisions. To reproduce this query, manually typing all of the references is hugely time consuming and prone to error.
How would I calculate the row sums and percentages without having to manually add everything up OR creating the query from [censored]?
dannyseager
You seem to have structural issues if that is the layout of your table.
You should have a structure something like
EmployeeAttendanceID (PK - Autonumber)
EmployeeID (fk)
AttendanceDate
Present (y/n)
monty666
Hi Dan,
Thanks for the input. While I appreciate that your approch is INFINITELY simpler, I'm faced with the fact that I have a nominal role where all employees are listed. In principle, all employees are supposed to attend all training sessions. My Boss wants a record of who was present, absent, Absent but on holiday, Absent but with permission (Excused) and N/A for those people who weren't part of the company yet. He also wants to have the attendance rate.
Believe me, a simple yes or no would be easy and prefereable but I'm trying to meet the requests of my boss. Furthermore, this problem is complicated by the fact that I will have many users entering the data through forms since my DB is split into FE/BE with user level security. So, for obvious reason I don't want to have several table with differing data for absent and present. Help!
jmcwk
Have you looked at creating a lookup table for your Absent,Holiday,Excused etc. and then creating a group by query to get your totals against the Class and or Student ?
monty666
Hi John,
So, I haven't tried that. How would I do that?
Monty
jmcwk
Monty,
Well I do not know how you have your structure set up for your table and you will have to play with it to see what occurs and for that matter it may not work for you at all but
1. Open a Query in design view
2. Select your table from the show table window
3. place your desired fields in the query
4. Click the Totals icon from the Menu Bar
5. You will notice that a Total line has been added to the query
6. Using the Pulldown below each field(s) select how you want the data Grouped aand displayed
Hopefully my explanation provides you with enough information to figure it out, if not get back to me OR attach a zipped copy of your DB no larger than 500kb and no sensitive data and i will take a look at it.