Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Using or Abusing DSUM ?

    I currently have a query displaying something to the following affect (simplified):

    Code:
    PeriodPast  GL_Number  Debits   Credits
    1              750-333      510      23
    2              750-333      400      25
    3              750-333      550      19
    4              750-333      300      21
    The PeriodPast is the number of periods/months prior to the current one and 2 years back (24 periods/months) are archived at any time for a given GL Number.

    What I would like to do is add 2 additional columns, one showing total debits for that PeriodPast and all other previous periods past debits. the other column would do the same thing but instead with credits.

    Basically, the results would need to look like this:

    Code:
    PeriodPast  GL_Number  Debits   Credits     TtlDebits   TtlCredits
    1              750-333      510      23            1760        88
    2              750-333      400      25            1250        65
    3              750-333      550      19            850          40
    4              750-333      300      21            300          21
    I tried doing this using DSUM but I'm not sure how you would state the criteria as I would need to say something like "PeriodPast >= PeriodPast" which obviously doesn't work stated this way.

    Also Say I pulled 2 GL numbers into this query at one time, is there a way to set multiple criteria for the two columns so that it does not total debits and credits across the other GL?

    Thanks for your help, this one has me stumped!

    Josh

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It would look like:

    "PeriodPast >= " & PeriodPast

    so the part outside the quotes would use the value from the current record. You can certainly add a criteria for account. There's an example at the bottom of this that demonstrates multiple criteria with mixed data types:

    General: DLookup Usage Samples
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 8
    I would create a query that sum up the Debits Credits by Gl_Number

    then JOIN the newly created Query to the first query by the Gl_number
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not sure that would work in this situation. The OP's requirement appears to be a running sum problem more than a simple sum problem.
    Paul

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Paul, Thanks that worked. It seems like it is running a little slow (chunking out each line separately, any ideas for how to speed up the running of this query? Any property setting which would help? Thanks! Also, can I set up multiple criteria on one DSUM function with AND?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Domain aggregate functions in queries are notorious performance hogs. Not sure if there's much you can do. I might explore taking that out of the query and using running sum textboxes in a report (the sort would need to be reversed). As to the "AND" issue, did you review the link and its example of one?
    Paul

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Paul,

    Thanks for the help, sorry, I missed that link before. It seems this query must be going back to the source table for each record to be taking this long. Maybe could setup a macro to make a table with the non-aggregate info then have it open a select query based off the table to generate the agregate info. Would think that would speed things up but this sort of functionality is new to me so I don't know.

    Another idea is to have 48 columns in the query (24 for Debit, 24 for Credit) and pull the Dsum for each month into each row. Not sure how I would then select the one I want. That would be too many embedded if/then statements.

    Josh

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The DSum() is basically another query, so for every record this query returns you're essentially running another query to get the summed data. That's why they're generally avoided in queries. Dumping the non-aggregated data into a temp table would probably help, as the DSum() would only be running against that temp table rather than the entire source table. Another option is to use the running sum property of a textbox.
    Paul

  9. #9
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    That's weird. If I create individual columns to look up the aggregated debits and/or credits up to a certain month per column (ie. D1 column shows totals for all debits, D2 column shows totals for all debits where periodpast is 2 or greater, ...etc), the query runs faster and does not chunk out the results line by line. But as soon as I have it lookup which column to use is when it starts chunking (sorry, I don't know a more technical word). Wouldn't think this last step would be the one to slow things down as the data is already there for each row and it isn't aggregating any more.

Posting Permissions

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