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:
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:
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
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.
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?
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?
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.
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.
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.