Running Amt Total Based on 3 Days (1 Viewer)

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
Hello,
Is there a way to calculate a running total on an amount field based on a running three day span (SettlmtDt field) in a query?
Then, I want to compare the new total amount to a static limit amount. If it’s over, I want to see the record.

The query has two tables; customer and transaction. They are linked on customerID.
Ex.
Cust1 $500.00 06/01/16 Cust1 Limit = 700.00
Cust1 $250.00 06/02/16
Cust1 $500.00 06/03/16
Cust1 $400.00 06/04/16
Cust1 $500.00 06/05/16

Cust2 $1500.00 06/01/16 Cust2 Limit = 2700.00
Cust2 $1250.00 06/02/16
Cust2 $500.00 06/03/16
Cust2 $1400.00 06/04/16
Cust2 $500.00 06/05/16

Cust3 $3500.00 06/01/16 Cust3 Limit = 5000.00
Cust3 $250.00 06/02/16
Cust3 $3500.00 06/03/16
Cust3 $100.00 06/04/16
Cust3 $500.00 06/05/16

The query would look at days 1+2+3, then 2+3+4, then 3+4+5 etc. and calculate the totals for each of the customers $amts on each of those day groups.

Query Results:

Cust1 $1250.00 06/03/16
Cust1 $1150.00 06/04/16
Cust1 $1400.00 06/05/16
Cust2 $3250.00 06/03/16
Cust2 $3150.00 06/04/16
Cust3 $7250.00 06/03/16

I don't know where I would even start.
Any help is greatly appreciated.:confused:
Thank you in advance.
 

stopher

AWF VIP
Local time
Today, 11:25
Joined
Feb 1, 2006
Messages
2,396
This will give you your result list. You can then join with your limit list and apply the limit criteria:

Code:
SELECT CustomerID, Amount, SettlmtDt, DSum("[Amount]","tblInvoices","customerID='" & [customerID] & "' and [SettlmtDt] between #" & [SettlmtDt]-2 & "# AND #" & [SettlmtDt] & "#") AS Result
FROM tblInvoices

hth
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
First, would you please explain how this section of the query works:

","customerID='" & [customerID]

I don't understand what this does in a query.

Thank you.
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
Hi,
I've entered the information.
I'm getting the error message, "The expression you entered has an invalid date value." On #" & [Settlm't Date]-2 & "#

Would you know what went wrong?

Thanks!:)
 

stopher

AWF VIP
Local time
Today, 11:25
Joined
Feb 1, 2006
Messages
2,396
Looks like you've got a speech mark in this:

[Settlm't Date]

Would that be the problem?
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
Not sure why that would make a difference if it's enclosed in brackets?
 

stopher

AWF VIP
Local time
Today, 11:25
Joined
Feb 1, 2006
Messages
2,396
The speech mark is a very strange character to have in a field name particularly as it didn't appear in the fieldname in your first post.

Yes the speech mark could cause problems because the expression in the criteria part of the DSUM function is built by building a string. The speech may well be interpretted as a string delimiter.
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
Thank you for that info. Good to know. I'm still fairly new at this.
I changed the field name and took our the speech mark.

I now get an error message stating:
"The expression you entered has an invalid date value."

Here is the full expression (with some name changes).

Result: DSum([DR $ Amt],[Daily Originations], [File Name] =' " & [COName10] & "' and [Settlm't Date] between #" & [SettlmtDt]-2 & "# AND #" & [SettlmtDt] & "#")
 

stopher

AWF VIP
Local time
Today, 11:25
Joined
Feb 1, 2006
Messages
2,396
I've added speech marks around [DR $ Amt] and change the square brackets for the table name to speech marks so [Daily Originations] becomes "Daily Originations".

I've also removed another speech mark from another Settlm't Date. Make sure you've removed it also from your table design.

There was also a space between ' and ". Imust have resulted from the forum editor but I've removed it anyway.

So we have:

Code:
Result: DSum("[DR $ Amt]","Daily Originations", [File Name] ='" & [COName10] & "' and [Settlmt Date] between #" & [SettlmtDt]-2 & "# AND #" & [SettlmtDt] & "#")

If you're stick stuck then post a simple version of your Access file with just the table and a few example records.
 

MarkK

bit cruncher
Local time
Today, 04:25
Joined
Mar 17, 2004
Messages
8,178
If the datatype of the field 'SettlmtDt' is a string, you may need to do this subtraction...
Code:
 between #" & [SettlmtDt][COLOR="Blue"]-2[/COLOR] & "# AND #"
...outside the date expression, like...
Code:
 between #" & [SettlmtDt] & "#[COLOR="Blue"] - 2[/COLOR] AND #"
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
Good morning,
I've updated the expression.
I'm now getting the error message; "The expression you entered has an invalid date value."

#" & [SettlmtDt]-2 & "#

The data type for this field is Date/Time.

I've also tried MarkK's suggestion with not luck and the same error message.

Do you know why this is?

Thank you so much for your help!!
 

stopher

AWF VIP
Local time
Today, 11:25
Joined
Feb 1, 2006
Messages
2,396
Maybe you could post a copy of your table in a new database and post here. The problem with this sort of problem is we can't see where you are starting from so make assumptions.
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
Hi,
I removed the # marks from the dates and no longer received an error message; however I'm not sure what I should see for the results.

I've attached a slimmed down version of the database for your review.

Thank you!
 

Attachments

  • Running Tot DB.accdb
    1 MB · Views: 56

MarkK

bit cruncher
Local time
Today, 04:25
Joined
Mar 17, 2004
Messages
8,178
In the Result field of the query, in your DSum() expression,
1) in the second parameter (domain), the table name is missing an opening square bracket.
You have...
Code:
...,"Daily Originations]",...
...it should be...
Code:
...,"[Daily Originations]",...
2) in the third parameter (criteria), the whole expression is missing it's opening quote, which explains why the embedded single quote was previously causing a problem.
You have...
Code:
...,[File Name]='" & [COName10] & "' And [SettlmtDt] Between...
...it should be...
Code:
...,"[File Name]='" & [COName10] & "' And [SettlmtDt] Between...
...and then you need to re-add your date delimiters.
Hope this helps.
 

NLR

Registered User.
Local time
Today, 07:25
Joined
Aug 29, 2012
Messages
71
I've made the updates and put the # bank in and now it's working correctly.

I also added a currency format.

Result: FormatCurrency(DSum("[DR $ Amt]","[Daily Originations]","[File Name]='" & [COName10] & "' And [SettlmtDt] Between #" & [SettlmtDt]-2 & "# AND #" & [SettlmtDt] & "#"))

Thank you so much for all your help!!
 
Last edited:

Users who are viewing this thread

Top Bottom