ACCESS FEED: Re: [MS_AccessPros] Report Balance on each row should be correct on date order

John,

Yes! Graham is right.

I did checked your last sql where you joined <= and it gave an error on some places not providing correct balance.

Any ways i am glad and thankful you and other MVP's took a keen interest and supportive for helping me in different ways and simplifying the query. I am also going to study Duane's suggestion also.

Once again thanks to all of you,

Regards,

Khalid

On Wednesday, August 6, 2014 12:47 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Duh. Thanks for the clarification, Graham!

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 5, 2014, at 2:21 PM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John

"TransactionDate <= " & Format([TransactionDate], "\#yyyy-mm-dd\#") &

" AND TransactionID <= " & [TransactionID]

is certainly simpler, but it does not give the same result. One of the things that were causing Khalid a problem was the fact that the TransactionID values are not necessarily in date order. In his sample, they went something like this:

6 01/01/14

7 01/01/14

21 27/01/14

22 27/01/14

34 30/01/14

36 07/02/14

39 20/02/14

30 27/02/14 <<<<<

44 27/02/14

Notice that #30 comes after #34, #36 and #39 chronologically. The simplified condition will eliminate those three records from the running sum, when they should be included. This is why the "<" and "=" date conditions need to be treated separately, with the TransactionID being considered only for the "=" condition:

"(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#") &
" OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#") &
" AND TransactionID <= " & [TransactionID] & "))" &

" AND ClientCIN = " & [ClientCIN]

All the best,

Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 6 August 2014 00:27
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report Balance on each row should be correct on date order

Khalid-

See also Graham's reply. Here is your SQL again a bit more simplified and laid out so that you can understand it better. Note that I eliminated separate < and = comparisons on TransactionDate and used a simpler <=.

SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate,

[Clients Transaction].TransactionType, [Clients Transaction].ClientCIN,

[Clients Transaction].ClientName, [Clients Transaction].ConsignmentNo,

[Clients Transaction].Cartons, [Clients Transaction].Weight, [Clients Transaction].Details,

[Clients Transaction].AmountReceived, [Clients Transaction].Expenses,

DSum("AmountReceived", "[Clients Transaction]",

"TransactionDate <= " & Format([TransactionDate], "\#yyyy-mm-dd\#") &

" AND TransactionID <= " & [TransactionID] &

" AND ClientCIN = " & [ClientCIN]) AS TotCash,

DSum("Expenses", "[Clients Transaction]",

"TransactionDate <= " & Format([TransactionDate], "\#yyyy-mm-dd\#") &

" AND TransactionID <= " & [TransactionID] &

" AND ClientCIN = " & [ClientCIN]) AS TotExpense,

Nz(DSum("AmountReceived", "[Clients Transaction]",

"TransactionDate <= " & Format([TransactionDate], "\#yyyy-mm-dd\#") &

" AND TransactionID <= " & [TransactionID] &

" AND ClientCIN = " & [ClientCIN]), 0)

-Nz(DSum("Expenses", "[Clients Transaction]",

"TransactionDate <= " & Format([TransactionDate], "\#yyyy-mm-dd\#") &

" AND TransactionID <= " & [TransactionID] &

" AND ClientCIN = " & [ClientCIN]), 0) AS Balance

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 5, 2014, at 4:42 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello Graham,

I read your observations and explanations carefully and tried to digest the suggestions you explained deeply.

Well i may have to admit that i am not so perfect in sql, the changes i made gives me syntax error, the sql is now:

SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].TransactionType, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, [Clients Transaction].ConsignmentNo, [Clients Transaction].Cartons, [Clients Transaction].Weight, [Clients Transaction].Details, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " AND TransactionID <= " & [TransactionID] & ")) & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction"(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " AND TransactionID <= " & [TransactionID] & ")) & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, Nz(DSum("AmountReceived","[Clients Transaction]","(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " AND TransactionID <= " & [TransactionID] & ")) AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]), 0)-Nz(DSum("Expenses","[Clients Transaction]","(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " AND TransactionID <= " & [TransactionID] & ")) AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]), 0) AS Balance FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

I am not sure i did the changes exactly you told. Please check it and point the mistake.

Regards,

Khalid

On Tuesday, August 5, 2014 12:16 PM, "Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Latest sql is:

SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].TransactionType, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, [Clients Transaction].ConsignmentNo, [Clients Transaction].Cartons, [Clients Transaction].Weight, [Clients Transaction].Details, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

In the meanwhile i'll check for Graham's suggestions also and let inform both of you.

Regards,

Khalid

On Tuesday, August 5, 2014 2:41 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

It looks like it is erroneously picking up transaction #30 which does have a lower transaction ID but does not have a lower date. Are you sure you're checking for both date and transaction ID in all DSum expressions? Please post the latest SQL.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 4, 2014, at 7:37 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yet there is some flaw in Balance, from TransactionID 6 to 34 Balance is OK. See against TransactionID 36 Balance is not correct.

TransactionID TransactionDate AmountReceived Expenses Balance

6 01/01/14 26,063 -26,063

7 01/01/14 807 -25,256

20 26/01/14 104 -25,360

21 27/01/14 250 -25,610

22 27/01/14 250 -25,860

34 30/01/14 7,000 -18,860

36 07/02/14 1,883 -20,795

39 20/02/14 350 -21,093

30 27/02/14 52 -25,912

44 27/02/14 1,555 -22,700

............ and more entries

Please look into it.

On Monday, August 4, 2014 4:45 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Ah, when you have multiple transactions on the same date, then you also need to sort on TransactionID.

SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].TransactionType, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, [Clients Transaction].ConsignmentNo, [Clients Transaction].Cartons, [Clients Transaction].Weight, [Clients Transaction].Details, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 4, 2014, at 12:15 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Sorry from me too, actually it was late night here when i received your last reply. Now there is no error in query.

But there is still some miscalculation in Balance i quote down few entries.

TransactionID TransactionDate AmountReceived Expenses Balance

6 01/01/14 26,063 -25,256

7 01/01/14 807 -25,256

20 26/01/14 104 -25,360

21 27/01/14 250 -25,860

22 27/01/14 250 -25,860

34 30/01/14 7,000 -18,860

36 07/02/14 1,883 -21,569

20/02/14 350 -21,093

30 27/02/14 52

-29,940

44 27/01/14 250 -25,860

............ and more entries

Please look into it.

regards,

Khalid

On Monday, August 4, 2014 12:25 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

Sorry, I missed changing one TransactionID to TransactionDate. You should have been able to spot that.

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 3, 2014, at 11:58 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Again there is syntax error:

Syntax error in date in query expression 'TransactionDate <=#10# AND ([Clients Transaction].ClientCIN= 5'.

While repeatedly clicking on error message OK button the above figure between #6# go on changing, and this is value of TransactionID. Then the report opens.

Column for Balance is now showing data. But i assume its not in order for example see the top two rows

TransactionID TransactionDate AmountReceived Expenses Balance

6 01/01/14 Null 26,063 807

7 01/01/14 807 Null 807

Khalid

On Sunday, August 3, 2014 9:23 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

I didn't notice that you have somehow lost the second parameter in all your DSum statements. Try this:

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 3, 2014, at 11:13 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

While opening the report got the following error message:

The Microsoft jet database engine cannot find the input table or query 'TransactionDate <=#01/01/14# AND [Clients Transaction].ClientCIN=5'. Make sure it exists and that its name is spelled correctly.

After long clicking on this message box OK button report opens but Balance column is showing zero value on each row. Although on report footer sum of "AmountReceived" and sum of "Expenses" is correct.

Khalid

On Sunday, August 3, 2014 8:03 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Kahlid-

Spotted an extra open paren in your first DSum and a few other anomalies:

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 3, 2014, at 7:55 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I am getting syntax error while saving the query and it is highlighting "Expenses"

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

Khalid

On Sunday, August 3, 2014 4:47 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

Try:

"TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 3, 2014, at 2:13 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John,

I have a report with Record source "ClientsBalanceSheetQuery"

FROM [Clients Transaction]

WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));

Sorting and grouping is "TransactionID"

At present with this query results are perfect, but now the requirement is that Sort order should be TransactionDate and Balance on each row should be according to TransactionDate.

I have tried changing sort order to TransactionDate but results are not OK, also on the above query at DSum statements i tried to change "TransactionID <=" & [TransactionID]" with "TransactionDate <=" & [TransactionDate]" but that also did'nt worked.

please help.

Regards,

Khalid Tanweer