Running Sum Using Query

[Ashfaque]

Hello,

This query created to produce Running Sum of the invoices of same customer. But even it producing double amount when there only one invoice for that particular customer. Previous I used this query and it was working. May be there has been changed somewhere which I didnt clear about.

Example:

Barratt Associates, this client has invoice of 130 only but ruining sum is showing 260
It should make all invoices amounts total when there are more than one invoices.

Where is wrong in my query? Can someone help me there.... I have attached the db.

Thanks in advance
Ashfaque

 

[pbaldy]

I'm not clear on the issue, the item you mentioned is only 130 when I run it:

 

[Ashfaque]

But at me it is strange....

 

[pbaldy]

You obviously have more/different data in that db than is in the sample. Does the sample work properly for you?

 

[Gasman]

I get

 

[pbaldy]

That's odd. I wonder if the untrusted status of the file on my computer is affecting the calculation. At a glance I don't see why it would.

 

[Gasman]

Expr1 is your calc that you add to Amount.

Nz(DSum("Amount","T_CrSalesInvoice","CustomerCode = " & [CustomerCode] & " And (InvDate < #" & [InvDate] & "# Or (InvDate = #" & [InvDate] & "# And InvNum < " & [InvNum] & "))"),0)

Then you can see how it happens

 

[Ashfaque]

Better I will attach here my FE and BE with min records and tbls etc so that it would be easy for you.

Please enter in db with Shift +Enter to see the tbls, queries, forms etc.

You may directly run form "F_CrSalesInvStatus_Master". This was created to clear OLDEST invoices as and when paid by customers..means adjustement of payment from invoice to invoice..

Take the example of Barratt Associates and add new payment in upper area...just enter date and amount...not need to feed other fields.

The strange thing is it showing overall total correct at the left text boxes. But not in 2 query grids....strange....

Regards,

 

[Gasman]

You need to work it out.
You will notice that for all the others the starting amount is 0 for the first record of the group.
I *thought* it might be because it is a single record, but you have others there that are single records and they have 0 for Expr1.

You need to determine why that is.

Broke it down even more.

Expr2: DSum("Amount","T_CrSalesInvoice","CustomerCode = " & [CustomerCode] & " And InvDate < #" & [InvDate] & "#")
Expr3: DSum("Amount","T_CrSalesInvoice","CustomerCode = " & [CustomerCode] & " AND  InvDate = #" & [InvDate] & "#  And InvNum < " & [InvNum])

Up to you now.

 

[Ashfaque]

Did some one run this form ?

When we receive payment 130, it should clear first circled invoice and then of 290 and 50.

The query is doubling amount at first record which I want to stop and accumulate from next same customer only then query result would be ok

Any other thoughts ?

You obviously have more/different data in that db than is in the sample. Does the sample work properly for you?

previously all the records were showing properly with same query but since I added Barratt Associates, it started producing the above errors....

 

[Gasman]

I got rid of fields not needed for this problem.
I am wondering if it is a date issue, as you have not formatted the dates, yet are using # ?

Yes, it is a date issue.

I changed Expr2 to be

DSum("Amount","T_CrSalesInvoice","CustomerCode = " & [CustomerCode] & " And InvDate < #" & Format( [InvDate],"mm/dd/yyyy") & "#")

and now get

So format your dates correctly.

 

[Gasman]

Last column is RunningAmount2

RunningAmount2: [Amount]+Nz(DSum("Amount","T_CrSalesInvoice","CustomerCode = " & [CustomerCode] & " And (InvDate < #" & Format([InvDate] ,"mm/dd/yyyy") & "# Or (InvDate = #" & Format([InvDate] ,"mm/dd/yyyy") & "# And InvNum < " & [InvNum] & "))"),0)

PBaldy is in the USA I believe, or at least uses the default Access date format.
That would explain why it works for him.

 

[Ashfaque]

Thanks Gasman,

that was the exact point I missed. My date format was different than the normally I used.

Thanks for pointing ....very much appreciated.....

 

[pbaldy]

PBaldy is in the USA I believe, or at least uses the default Access date format.
That would explain why it works for him.

Correct on both counts, but I'm aware of that issue and should have noticed it.

 

[Ashfaque]

How about if we need both British and USA date format to work....I mean "dd/mm/yyyy" or "mm/dd/yyyy"

Could it be ?

 

[Gasman]

Dates are just numbers, so keep it as it is or use format yyyy-mm-dd.

 

[pbaldy]

How about if we need both British and USA date format to work....I mean "dd/mm/yyyy" or "mm/dd/yyyy"

Could it be ?

You can format any way you want for users, it's vba that requires US format:

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

 

[Pat Hartman]

How about if we need both British and USA date format to work....I mean "dd/mm/yyyy" or "mm/dd/yyyy"

NEVER format dates that you will later use to sort on or compare. WHY? because formatting them turns them into a string. Strings sort like strings. Character by character, left to right. So, 01/02/2023 is less than 02/01/2020 because 01 is less than 02. Internally, dates are stored as double precision numbers with the integer portion representing the number of days since 12/30/1899 (for Jet/ACE) and the decimal representing a point in time. That means 12/30/1899 6 PM would = 0.75 12/31/1899 6 AM 2 would be 1.25. 12/29/1899 12 PM would be -1.5.

If you have to format the date because you are creating the SQL in a string, you need to use an unambiguous format such as yyyy/mm/dd OR use the US standard of mm/dd/yyyy since that is what SQL Server assumes if you give it an ambiguous string. Is 01/02/2023 Jan 2 or Feb 1? SQL Server will assume Jan 2.