Count(Distinct...) equivalent in Access 2000


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Count(Distinct...) equivalent in Access 2000

  1. #1
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557

    Count(Distinct...) equivalent in Access 2000

    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:

    Code:
    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:

    Code:
    
    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
    A balanced diet is a cookie in each hand.

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Try something like this:
    Code:
    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.
    Last edited by Phil Weber; 01-30-2006 at 05:45 PM.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    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
    A balanced diet is a cookie in each hand.

  4. #4
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    Oh yeah......

    THANK YOU!
    A balanced diet is a cookie in each hand.

Similar Threads

  1. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 06:01 PM
  2. ACCESS XP, SQL Server 2000 Stored Procedures
    By Mike in forum VB Classic
    Replies: 0
    Last Post: 08-11-2001, 10:44 PM
  3. Office 97 db failure if Access 2000 installed
    By Kathleen Dollard-Joeris in forum VB Classic
    Replies: 8
    Last Post: 05-23-2000, 11:54 PM
  4. Replies: 2
    Last Post: 04-19-2000, 04:34 AM
  5. Access 2000 automation from VB6
    By Mak Foka in forum authorevents.kurata
    Replies: 3
    Last Post: 04-18-2000, 09:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center