Re: How to count records w/o counting duplicates by week, month, or ye
- From: John Spencer <spencer@xxxxxxxxxx>
- Date: Thu, 16 Apr 2009 19:10:24 -0400
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
'====================================================
John wrote:
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!
- References:
- Prev by Date: RE: Duplicate Record in query results - show only once
- Next by Date: Re: Crosstab design, year overlaps / combined
- Previous by thread: How to count records w/o counting duplicates by week, month, or ye
- Next by thread: RE: How to count records w/o counting duplicates by week, month, or ye
- Index(es):
Relevant Pages
|
