Running Amt Total Based on 3 Days

[NLR]

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.
Thank you in advance.

 

[Uncle Gizmo]

I don't know...

But I'd start with Rogers Access Library, in particular the Running sum example:-

http://www.rogersaccesslibrary.com/...l?SID=1ad20d6294bfb7bf5d764e964fca75638888889

 

[stopher]

This will give you your result list. You can then join with your limit list and apply the limit criteria:

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

hth

 

[NLR]

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]

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]

Looks like you've got a speech mark in this:

[Settlm't Date]

Would that be the problem?

 

[NLR]

Not sure why that would make a difference if it's enclosed in brackets?

 

[stopher]

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]

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]

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:

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]

If the datatype of the field 'SettlmtDt' is a string, you may need to do this subtraction...

 between #" & [SettlmtDt][COLOR="Blue"]-2[/COLOR] & "# AND #"

...outside the date expression, like...

 between #" & [SettlmtDt] & "#[COLOR="Blue"] - 2[/COLOR] AND #"

 

[NLR]

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]

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]

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!

 

[MarkK]

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

...,"Daily Originations]",...

...it should be...

...,"[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...

...,[File Name]='" & [COName10] & "' And [SettlmtDt] Between...

...it should be...

...,"[File Name]='" & [COName10] & "' And [SettlmtDt] Between...

...and then you need to re-add your date delimiters.
Hope this helps.

 

[NLR]

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!!