Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20

    DSUM in Query assistance for total of specific rows is correct.

    Hello, I'm looking for some help regarding a query i've build to retrieve employee work hours. I'm new to access and in this case(query) i want to retrieve a specific employee and identify the total # of "vacation hours" that he has taken as of the begining of this year. The query retrieves the correct employee and all his vacation day/hours for this year but the dsum hours are incorrect showing 216 when it should be only "40" hours. Am i using the wrong function, any assistance would be helpful.



    My query Criteria is set to:
    empoyeeID prompted entered 602DateWorked set to >#12/31/2013#
    WorkCodeID set to 12 (code for Vacation Day )
    An Expression set for totaling up the Hours worked =
    VacationRuntot: (DSum("[Time Card Hours].[BillableHours]","Time Card Hours","[Time Card Hours].[WorkCodeID] = 12"))

    Query results are sum of “Vacation returns:“216” ? but should be “40” in the VacationRuntot field:
    Employee Number Employee ID Date Worked Work Code ID SumOfBillableHours VacationRuntot
    602 Billeck, Robert 01/20/2014 Vacation 8 216
    602 Billeck, Robert 02/03/2014 Vacation 8 216
    602 Billeck, Robert 02/24/2014 Vacation 8 216
    602 Billeck, Robert 05/09/2014 Vacation 8 216
    602 Billeck, Robert 05/12/2014 Vacation 8 216

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this (untested********)

    Original
    VacationRuntot: (DSum("[Time Card Hours].[BillableHours]","Time Card Hours","[Time Card Hours].[WorkCodeID] = 12"))

    VacationRuntot: DSum("BillableHours","Time Card Hours","WorkCodeID =" & 12)

  3. #3
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20
    Thank you but i've had/have tried this and i still get the same results:

    Employee Number Employee ID Date Worked Work Code ID SumOfBillableHours VacationRuntot
    602 Billeck, Robert 01/20/2014 Vacation 8 216
    602 Billeck, Robert 02/03/2014 Vacation 8 216
    602 Billeck, Robert 02/24/2014 Vacation 8 216
    602 Billeck, Robert 05/09/2014 Vacation 8 216
    602 Billeck, Robert 05/12/2014 Vacation 8 216

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post the sql view of the query.

  5. #5
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20
    ?In sql view has the following:

    SELECT Employees.EmployeeNumber, [Time Cards].EmployeeID AS [Time Cards_EmployeeID], [Time Card Hours].DateWorked, [Time Card Hours].WorkCodeID, Sum([Time Card Hours].BillableHours) AS SumOfBillableHours, DSum("BillableHours","Time Card Hours","WorkCodeID =" & 12) AS VacationRuntot
    FROM (Employees INNER JOIN [Time Cards] ON Employees.[EmployeeID] = [Time Cards].[EmployeeID]) INNER JOIN [Time Card Hours] ON [Time Cards].[TimeCardID] = [Time Card Hours].[TimeCardID]
    GROUP BY Employees.EmployeeNumber, [Time Cards].EmployeeID, [Time Card Hours].DateWorked, [Time Card Hours].WorkCodeID, DSum("BillableHours","Time Card Hours","WorkCodeID =" & 12)
    HAVING (((Employees.EmployeeNumber)=[Enter Employee #:]) AND (([Time Card Hours].DateWorked)>#12/31/2013#) AND (([Time Card Hours].WorkCodeID)=12));

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I 'm trying to simplify things but I have not tried to recreate your tables.

    For a test, I've tried to remove the Employees table.

    Could you create a a new query, go to sql view and paste in this

    Code:
    SELECT  [Time Cards].EmployeeID 
    , [Time Card Hours].DateWorked
    , [Time Card Hours].WorkCodeID
    , Sum([Time Card Hours].BillableHours) AS SumOfBillableHours, 
    FROM    [Time Cards]  INNER JOIN [Time Card Hours] ON 
    [Time Cards].[TimeCardID] = [Time Card Hours].[TimeCardID]
    
    GROUP BY 
    [Time Cards].EmployeeID, [Time Card Hours].DateWorked, [Time Card Hours].WorkCodeID, 
    HAVING
    ((([Time Cards].EmployeeID )=[Enter Employee #:]) AND (([Time Card Hours].DateWorked)>#12/31/2013#) AND (([Time Card Hours].WorkCodeID)=12));

    Hopefully [Time Cards].EmployeeID )=[Enter Employee #:] is the same value as
    Employees.EmployeeNumber)=[Enter Employee #:]
    Good luck.

  7. #7
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20
    unfortuately it doesn't as EmployeeId in TimeCards is the Employees name where the Employee # in the Employee table is a # ex 602 etc. This data base is actually a MicroSoft Access template called "Time & Billing Database" just modified to our usage to only use the time keeping portion as we don't need everything else.

    Time Card Hours table ex:
    Time Card Detail ID Time Card ID Date Worked Project Work Description Billable Hours Billing Rate Work Code ID TimeStartEnd
    45 11 1/1/2014 Holiday 8 Holiday
    46 11 1/2/2014 Regular Work Day 8 Regular Hours
    47 11 1/3/2014 Regular Work Day 8 Regular Hours
    135 29 2/3/2014 Vacation 0 Vacation
    The Time Card table is tired to the Time Card Hours by the Time Card Detail ID

    Example of Employee table:

    Employee ID Employee Number First Name Last Name
    1 1 Lene Aalling - Test
    6 699 Sally Liu
    7 601 Paul Schafernak
    8 602 Robert Billeck
    9 603 Damian Noga


    So i did plug in the sql you provided but an error accured: "the select stmt includes a reserve word or an agrument name that is mispelled or missing, or the punchation is incorrected.

    thanks

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I mocked up your Time tables and used Employee 602
    I omitted the Employees Table and used this code to get the Daily Billable Hours with WorkCode 12

    Here are my sample tables

    EmployeeId TimeCardid
    602 1
    TimeCardIhoursId BillableHours dateWorked workCodeId TimeCardId
    1 8 20/01/2014 12 1
    2 8 02/03/2014 12 1
    3 8 24/02/2014 12 1
    4 8 05/09/2014 12 1
    5 8 05/12/2014 12 1
    6 7 05/12/2014 23 1
    7 5 23/03/2014 3 1

    Code:
    SELECT TimeCard.EmployeeId
    , Sum(tblTimeCardHours.BillableHours) AS SumOfBillableHours
    , tblTimeCardHours.dateWorked
    , tblTimeCardHours.workCodeId
    FROM 
    tblTimeCardHours INNER JOIN TimeCard ON
     tblTimeCardHours.TimeCardId = TimeCard.TimeCardid
    GROUP BY 
    TimeCard.EmployeeId, tblTimeCardHours.dateWorked, tblTimeCardHours.workCodeId
    HAVING 
    (((TimeCard.EmployeeId)=602) AND ((tblTimeCardHours.workCodeId)=12))
    It doesn't need DSum.

    To get the total hours
    Code:
    Select EmployeeID, Sum (SumOfBillableHours)
    From
    (
    
    The SQL above goes here
    
    )
    Group By EmployeeID;
    You can add in your Employees Table if you want.

    But try this sample and see how it works. I'll help with adding in the employees table, if you need help.

    I called the final query "qryBillableTime".

    EmployeeID SumOfSumOfBillableHours
    602 40

  9. #9
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20
    Hello there, sorry for the delay. I have done as you asked above and entered the SQL as you mentioned however i receive an error stating that "Microsoft access cannot fine the input table or query 'tblTimeCardHours'. Make sure it exsist and that it name is spelled correclty." The name is correct, i've attach a screen print below if u can see it?!. So I tried to add spaces between the words & underscores, remove the "tbl" and I get the same message. In your message you stated that i don't need to do a "DSum" so is writing a "sql query" the only way i can get the accumlative totals?

    Click image for larger version. 

Name:	q.jpg 
Views:	28 
Size:	186.6 KB 
ID:	15535

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post your sql directly in your post (so I can copy and edit).

    The SQL I gave you was based on my Mock up of your situation.
    I used tables
    tblTimeCardHours and TimeCard

    but your tables have different names. You have to make the sql adjustments to reflect your table names.
    I recommend you adopt a naming convention that does not allow embedded spaces -- it will save you numerous
    fomatting/syntax errors over time.

  11. #11
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Expert is already helping you out. I just want to add my bit to your initial requirement
    i want to retrieve a specific employee and identify the total # of "vacation hours" that he has taken as of the begining of this year
    The DSum is yielding incorrect results because probably, it is summing all the values for a particular employee and not for this year as desired by you though the query is pulling results for this year only. You will need to include the year in criteria for DSum like .
    VacationRuntot: DSum("BillableHours","Time Card Hours","WorkCodeID =" & 12 & " AND Year(Date Worked)=" & 2014)

  12. #12
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20
    I realized! So i entered the following:

    SELECT
    EmployeeID
    ,Sum (SumOfBillableHours)
    From
    (SELECT TimeCards.EmployeeID
    , Sum(TimeCardHours.BillableHours) as SumOfBillableHours
    , TimeCardHours.DateWorked
    , TimeCardHours.WorkCodeID
    FROM
    TimeCardHours INNER JOIN TimeCard ON TimeCardHours.TimeCardID = TimeCard.TimeCardID
    GROUP BY
    TimeCards.EmployeeID,
    TimeCardHours.DateWorked,
    TimeCardHours.WorkCodeID
    Having (((TimeCards.EmployeeID)=602 AND ((TimeCardHours.WorkCodeID)=12)))
    Group By EmployeeID

    It's giving me a "syntax error in From clause" ??

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by SalVOM View Post
    I realized! So i entered the following:

    SELECT
    EmployeeID
    ,Sum (SumOfBillableHours)
    From
    (SELECT TimeCards.EmployeeID
    , Sum(TimeCardHours.BillableHours) as SumOfBillableHours
    , TimeCardHours.DateWorked
    , TimeCardHours.WorkCodeID
    FROM
    TimeCardHours INNER JOIN TimeCard ON TimeCardHours.TimeCardID = TimeCard.TimeCardID
    GROUP BY
    TimeCards.EmployeeID,
    TimeCardHours.DateWorked,
    TimeCardHours.WorkCodeID
    Having (((TimeCards.EmployeeID)=602 AND ((TimeCardHours.WorkCodeID)=12)))
    )
    Group By EmployeeID

    It's giving me a "syntax error in From clause" ??

    The Orange () seem mismatched.
    The red and blue table names are from the Join statement but the green are names that don't match your table names.

    I didn't add in the Date criteria, but it should be there if you want5 only dates >#12/31/2013#

  14. #14
    SalVOM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    20
    My appologies but is there another way other than writing sql to get the result i need? i've studied the sql and updated my query to use all the tables/ field I need using what was provided and get a result now but its not summing up the amounts. So if there is something easier I can use that would be greatly appreciated as finding the sum total for 'vacation code of 12" isn't the only one I have to fine I also have codes 7,9 &10 per employees.
    Thanks.

    Here is what i re-ran:
    SELECT Employees.EmployeeNumber,
    [Time Cards].EmployeeID AS [Time Cards_EmployeeID],
    Sum([Time Card Hours].BillableHours) AS SumOfBillableHours,
    [Time Card Hours].DateWorked, [Time Card Hours].WorkCodeID
    FROM (Employees INNER JOIN [Time Cards] ON Employees.[EmployeeID] = [Time Cards].[EmployeeID]) INNER JOIN [Time Card Hours] ON [Time Cards].[TimeCardID] = [Time Card Hours].[TimeCardID]
    GROUP BY Employees.EmployeeNumber,
    [Time Cards].EmployeeID,
    [Time Card Hours].DateWorked,
    [Time Card Hours].WorkCodeID
    HAVING (((Employees.EmployeeNumber)=[Enter Employee #:]) AND (([Time Card Hours].DateWorked)>#12/31/2013#) AND (([Time Card Hours].WorkCodeID)=12));

    The result was:
    Employee Number Employee ID SumOfBillableHours Date Worked Work Code ID
    602 Billeck, Robert 8 01/20/2014 Vacation
    602 Billeck, Robert 8 02/03/2014 Vacation
    602 Billeck, Robert 8 02/24/2014 Vacation
    602 Billeck, Robert 8 05/09/2014 Vacation
    602 Billeck, Robert 8 05/12/2014 Vacation






  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    SalVOM,

    Your sample code is missing the outermost Select statement --the one that does the Summing?
    The code before the orange ( and the code after the )

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-31-2012, 06:25 PM
  2. Replies: 3
    Last Post: 10-17-2012, 09:24 AM
  3. Replies: 3
    Last Post: 09-06-2012, 03:35 PM
  4. 'Total rows' in query
    By sk88 in forum Access
    Replies: 4
    Last Post: 08-29-2011, 09:31 AM
  5. Returning correct rows in a join/nested query
    By goneaccessing in forum Queries
    Replies: 5
    Last Post: 03-03-2010, 12:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums