Count(Distinct...) equivalent in Access 2000

[Laurel]

I'll try to explain this as well as I can....

I have a table that houses the number of hours each employee works. In another table, I have personal info on the employee. One of the fields (yes/no) indicates whether this person receives a per diem each day.

KINDA Like this:

HourlyDataTable
EmpName   DateofService   HoursWorked   (and more....)
Joe Blow    1/31/06            8

EmployeeInfoTable
Employee   HourlyWage       PerDiem
Joe Blow      $20.00               Yes 

What I ORIGINALLY had was query that calculated the numbers of hours worked * hourly wage AND, if the guy got a per diem, number of days worked * per diem amount (in another table). I also have it grouped by employee so that, on the report, I have a total number of hours, pay, etc. for the date range on the report. That worked really well until I realized that if the guy had two entries for one day, he got two per diems for that day! This COULD happen if the guy worked on two different projects in one day as they have to be separated by the data entry ppl.

How do I do this? I found Count(Distinct....) online but I can't use it in Access.

Here's my SQL statement:

SELECT HourlyData.CompanyName, HourlyData.EmpName, HourlyData.Class, HourlyData.Social, HourlyData.AFE, Sum(HourlyData.StraightHours) AS StraightTotal, Sum([HourlyData].[StraightHours])*[JobClassifications].[StraightRate] AS StraightCost, Sum(HourlyData.OTHours) AS OTTotal, Sum([HourlyData].[OTHours])*[JobClassifications].[OtRate] AS OTCost, Employees.PerDiem, Sum([StraightHours]+[OTHours]) AS TotalHoursWorked, Sum(([StraightHours]*[StraightRate])+([OTHours]*[OTRATE])) AS LaborCost, IIf([Perdiem]=True,Sum([PerDiemRate]),0) AS PerDiemCost, AFEs.Description, AFEs.AFEAmount
FROM ((HourlyData INNER JOIN JobClassifications ON (HourlyData.Class = JobClassifications.Classification) AND (HourlyData.CompanyName = JobClassifications.CoName)) LEFT JOIN AFEs ON HourlyData.AFE = AFEs.AFENo) INNER JOIN Employees ON (HourlyData.CompanyName = Employees.Comp) AND (HourlyData.Social = Employees.SSNo)
WHERE (((HourlyData.DateofService) Between [forms]![Reportsform]![txtFromDate] And [forms]![ReportsForm]![txtTodate]))
GROUP BY HourlyData.CompanyName, HourlyData.EmpName, HourlyData.Class, HourlyData.Social, HourlyData.AFE, Employees.PerDiem, AFEs.Description, AFEs.AFEAmount, JobClassifications.StraightRate, JobClassifications.OTRate;

Thanks GUYS!
L

[Phil Weber]

Try something like this:

SELECT 
   EmpDailyTotals.SSN, Employees.Employee, 
   EmpDailyTotals.DateOfService, EmpDailyTotals.StraightTotal, 
   IIf(Employees.PerDiem, PerDiemRate, 0) AS PerDiemCost
FROM
   (SELECT 
      Employees.SSN, HourlyData.DateOfService, 
      Sum(HourlyData.HoursWorked) AS StraightTotal 
   FROM HourlyData INNER JOIN Employees ON 
      (HourlyData.Social = Employees.SSN)
   GROUP BY Employees.SSN, HourlyData.DateOfService) 
   AS EmpDailyTotals
INNER JOIN Employees ON 
   EmpDailyTotals.SSN = Employees.SSN
WHERE 
   EmpDailyTotals.DateOfService BETWEEN 
   [forms]![ReportsForm]![txtFromDate] AND [forms]![ReportsForm]![txtToDate];

The key is that if you want to mix aggregate (Sum, GROUP BY, etc.) and non-aggregate data (e.g., PerDiem), you must do the aggregation in a subquery.

[Laurel]

WOW PHIL! I didn't think I was gonna be able to do this, but you're an SQL GENIUS!

(I'm still tellin' your mom about the other thing, though!)

L

[Laurel]

Oh yeah......

THANK YOU!