Results 1 to 7 of 7

Thread: DSum Function

  1. #1
    Join Date
    Feb 2017
    Posts
    3

    Answered: DSum Function

    Hi
    I am new with Access Database and I need your help with this aggregate function. I am trying to add a field with various criteria and when I am adding the second one, it does not return the correct information.
    The following is the query that I am using. I also need to add those numbers base on a date range field

    DSumAmt: DSum("[deductionamount]","tbpayrolldeduction","[employeeID]=" & [tbpayrolldeduction]![EmployeeID] And "[benefitsID]=" & [tbpayrolldeduction]![BenefitsId])

  2. Best Answer
    Posted by myle

    "MikeTheBike is right

    I would create a Query "Deductionamount" group it by the EmployeeID and do the maths bits

    then Link that query by the EmployeeID back to the Query you are trying to run and delete the Dsum's

    look like you are missing some " in the crit side of the dsum

    ,"[employeeID]=" & [tbpayrolldeduction]![EmployeeID] & " And [benefitsID]=" & [tbpayrolldeduction]![BenefitsId])"


  3. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    804
    Provided Answers: 2
    Hi

    Without seeing the full query it is difficult to make any definitive statement, but using aggregate function in a query is not a good idea (they are slow if you have any significant number if records).

    There is usually a better way of doing these things.

    MTB

  4. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    MikeTheBike is right

    I would create a Query "Deductionamount" group it by the EmployeeID and do the maths bits

    then Link that query by the EmployeeID back to the Query you are trying to run and delete the Dsum's
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  5. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    MikeTheBike is right

    I would create a Query "Deductionamount" group it by the EmployeeID and do the maths bits

    then Link that query by the EmployeeID back to the Query you are trying to run and delete the Dsum's

    look like you are missing some " in the crit side of the dsum

    ,"[employeeID]=" & [tbpayrolldeduction]![EmployeeID] & " And [benefitsID]=" & [tbpayrolldeduction]![BenefitsId])
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  6. #5
    Join Date
    Feb 2017
    Posts
    3

    Thumbs up

    Quote Originally Posted by myle View Post
    MikeTheBike is right

    I would create a Query "Deductionamount" group it by the EmployeeID and do the maths bits

    then Link that query by the EmployeeID back to the Query you are trying to run and delete the Dsum's

    look like you are missing some " in the crit side of the dsum

    ,"[employeeID]=" & [tbpayrolldeduction]![EmployeeID] & " And [benefitsID]=" & [tbpayrolldeduction]![BenefitsId])

    Thanks for your help

  7. #6
    Join Date
    Feb 2017
    Posts
    3

    Thumbs up

    Quote Originally Posted by MikeTheBike View Post
    Hi

    Without seeing the full query it is difficult to make any definitive statement, but using aggregate function in a query is not a good idea (they are slow if you have any significant number if records).

    There is usually a better way of doing these things.

    MTB


    Thanks

    What alternative you have in mind

  8. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    804
    Provided Answers: 2
    Hi VSolano

    As I said, without seeing the query it is almost imposible to second guess what you are trying to do, but would this query do anyting like what you want. If not, why not.
    Code:
    SELECT Sum(tbpayrolldeduction .deductionamount) As DSumAmt, tbpayrolldeduction.employeeID, tbpayrolldeduction.benefitsID FROM tbpayrolldeduction GROUP BY  tbpayrolldeduction.employeeID, tbpayrolldeduction.benefitsID
    MTB

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
  •