How to count records w/o counting duplicates by week, month, or ye

[John]

I have a table with payroll data. I have an employe ID, a week ending date
(Sunday), and hours worked on Sun, Mon, Tue, ... Sat. What I need is a query
that counts the number of employees, and gives total hours worked on what
days. The catch is that overtime and double-time hours are shown on a
different record, with the same week ending date. If I do a simle count of
employee ID, I get 2x the number of real employees. Not all employees work
over time or double-time.

Fields:
WeDte: week ending date
EmpID: Employee ID (text)
Mon: Monday Hours
Tue: Tuesay Hours
Etc. through "Sat: Saturday Hours"
WgRate: Employee Wage Rate

What I would like to do is to group the qery by week, month, quarter, or
year to get the total hours, salary cost, and employee count. The hours and
salary cost is not an issue, but counting the first EmpID in the month, and
then not counting it until the next month is killing me!

 

[John Spencer]

How about using something like this as your base query.

SELECT EmpID, WeDte,
Sum(Mon) as TMon, Sum(Tue) as TTue, Sum(Wed) as TWed, Sum(Thu) as TThu,
Sum(Fri) as TFri, Sum(Sat) as TSat, Sum(Sun) as TSun
FROM PAYROLL
GROUP BY EmpID, WeDte

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

Try these queries --
qryHous
SELECT WeDte, EmpID, Sum(Nz([Mon],0)+ Nz([Tue],0)+ Nz([Wed],0)+ Nz([Thu],0)+
Nz([Fri],0)+ Nz([Sat],0)+ Nz([Sun],0)) AS Hours, WgRate
FROM PayRoll
GROUP BY WeDte, EmpID, WgRate;

qryWeekHours
SELECT WeDte, Count([EmpID]) AS CountEmp, Sum([Hours]) AS WeekHours,
([WgRate] * Sum([Hours])) AS WageCost
FROM qryHours
GROUP BY WeDte;

qryMonthHours
SELECT Format([WeDte], "yyyymm") AS Year_Mon, Sum([CountEmp]) AS MonthEmp,
Sum([WeekHours]) AS MonthHours, Sum([WageCost]) AS MonthWages
FROM qryWeekHours
GROUP BY Format([WeDte], "yyyymm");

 

[Dale Fye]

Another method would be to create a normalizing query, to get your data in a
format that would be far easier to do most of these calculations. It would
look something like:

SELECT WeDte, EmpID, WgRate, "Mon" as DayOfWeek, [Mon] as HoursWorked
FROM yourTable
WHERE [MON] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Tue" as DayOfWeek, [Tue] as HoursWorked
FROM yourTable
WHERE [Tue] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Wed" as DayOfWeek, [Wed] as HoursWorked
FROM yourTable
WHERE [Wed] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Thu" as DayOfWeek, [Thu] as HoursWorked
FROM yourTable
WHERE [Thu] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Fri" as DayOfWeek, [Fri] as HoursWorked
FROM yourTable
WHERE [Fri] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Sat" as DayOfWeek, [Sat] as HoursWorked
FROM yourTable
WHERE [Sat] is not NULL

Once you have your data in this structure you can do almost anything with
it. You might even want to add a DateWorked column that subtracts the
appropriate number of days from the WeDte field. This would give you that
added ability of summing hours by month, not the month of the WeDte.

 

[John]

All very helpful... If I have a follow-up I'll post back..