Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Unanswered: Help with Access query

    Hi I'm writing a web program that uses an Access database and I'm having a problem with a query I'm trying to build. The query contains 4 tables:

    [Time Cards]
    TimeCardID
    EmployeeID
    DateEntered

    [Time Card Hours]
    TimeCardDetailID
    TimeCardID
    DateWorked
    BillableHours

    [Employees]
    EmployeeID
    FirstName
    LastName
    DivisionID

    [Division]
    DivisionID
    DivisionName

    I'm trying to get the output to look like

    (LastName) (Division) (#TimeCardsID) (#TimeCardsID*40) (sumBillableHours) and (% between (#TimeCardsIDs*40 AND sumBillableHours))

    I can create two queries that each do half but I don't know how to put them together:

    Name, Division, sumBillableHours HALF
    SELECT Employees.LastName, Division.DivisionName, Sum([Time Card Hours].BillableHours) AS SumOfBillableHours
    FROM Division
    INNER JOIN (Employees
    INNER JOIN ([Time Cards]
    INNER JOIN [Time Card Hours]
    ON [Time Cards].TimeCardID=[Time Card Hours].TimeCardID)
    ON Employees.EmployeeID=[Time Cards].EmployeeID)
    ON Division.Divisionid=Employees.DivisionID
    WHERE ((([Time Cards].DateEntered)>=#1/1/2004#
    And ([Time Cards].DateEntered)<=#3/3/2004#))
    GROUP BY Employees.LastName, Division.DivisionName;

    Name, Division, #TimeCards HALF
    SELECT Employees.LastName, Division.DivisionName,
    count([Time Cards].TimeCardID) AS TCount
    FROM Division
    INNER JOIN (Employees
    INNER JOIN [Time Cards]
    ON Employees.EmployeeID=[Time Cards].EmployeeID)
    ON Division.DivisionID=Employees.DivisionID
    WHERE [Time Cards].DateEntered Between #1/1/2004# And #3/3/2004#
    GROUP BY Employees.LastName, Division.DivisionName;

    I know I'm asking a lot but I've been trying to make this work for a while now.


    Thanks for anyones help
    Rob

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    This reply has been marked for deletion.
    Last edited by r123456; 11-15-04 at 06:09.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    SELECT e.LastName, d.DivisionName, TCH_VIEW.SBH AS SumOfBillableHours, tc.TCOUNT AS TCount
    FROM Division d 
    INNER JOIN (Employees e
    INNER JOIN 
    (
      Select tc.TimeCardID, Count(*) As TCOUNT
      From [Time Card] tc
      Where tc.DateEntered >= #1/1/2004# And tc.DateEntered <= #3/3/2004#
      Group By tc.TimeCardId 
    ) tc 
    INNER JOIN 
    (
      Select tch.TimeCardId, SUM(tch.BillableHours) As SBH
      From [Time Card Hours] tch
      Group By tch.TimeCardID
    ) TCH_VIEW
    ON tc.TimeCardID= TCH_VIEW.TimeCardID)
    ON Employees.EmployeeID=tc.EmployeeID) 
    ON d.Divisionid=e.DivisionID
    Last edited by r123456; 11-15-04 at 06:54.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •