Hello Graham,
That is a great way to move a novice a step ahead by telling the logic and to do the rest by himself. The same happened when in 1995 i first started building a software for our Stores in FoxPro DOS 2.6, while at that time i even did'nt knew about DOS. My colleague having good knowledge of programming referred me a book of an Indian author to study it. We had only one PC in our store at that time and lying as a virgin wearing a veil (covered with white cloth sheet). What i was doing at that time, i sit on the table at late night, read the codes and write them on paper of sheet according to my requirements. Then i used to go to my work place an hour early before work starts and try my code, and also stay one or two hours late after close of wok. Working on this pattern i observed that there were many misprints and wrong definitions in the book. Finally within one and half minute i completed my database as i had challenged my boss that all reports which we were making manually should be printed by our program.
During development of that software i was hanged at many places like DO WILE, LOOPS etc and i did rushed to my friend during lunch breaks and many many thanks to him that he only guided me with the logics and asked me to do it myself if still there was some problem he will guide me (same as you said in your last reply). I did a lot of work in FoxPro DOS 2.6. Did tried and experimented many logics and got desired results by my own. Unfortunately at those times i did not have access to internet and not knew about helping groups or forums.
Finally in 2008 i stepped into Access 2003 and build my FoxPro applications in Access and with the support, guidance and help of this group i learnt a lot, what so ever work i am doing now a days its credit goes to the group and all MVP's who have been kind enough to me. Thanks to all once again.
Now coming to our Sql, Wow that's great now its giving perfect results with your guidance, i am putting down my final Sql:
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 ClientCIN= " & [ClientCIN]
) AS TotCash, DSum(
"Expenses",
"[Clients Transaction]",
"(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
& " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#")
& " AND TransactionID <= " & [TransactionID]
& ")) 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]));
Thank you Graham,
Regards,
Khalid
On Tuesday, August 5, 2014 4:17 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hello Khalid
You have made a good start, but some of your SQL has become really garbled. For example, you have:
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
The DSum function takes three arguments: FieldName, TableName and Criteria. Currently, your TableName is:
"[Clients Transaction(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
which would give you something like this:
"[Clients Transaction(TransactionDate < #2014-01-01#"
Naturally, as there is no table of this name, it results in an error.
What you need is this:
DSum(
"AmountReceived",
"[Clients Transaction]",
"(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
& " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#")
& " AND TransactionID <= " & [TransactionID]
& ")) AND ClientCIN= " & [ClientCIN]
) AS TotCash
It is not strictly necessary to spread it out like this, but it makes it easier to read!
The other DSum expressions have similar (but not identical!) problems.
See if you can apply these principles to correct your SQL by yourself. If not then I'll be glad to help further, but I think you will be more satisfied if you fix it yourself. :-)
By the way, I recommend you try the version using the subquery that I suggested, instead of using DSum.
All the best,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 5 August 2014 21:42
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report Balance on each row should be correct on date order
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
39
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.
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 ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# 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 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:
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 ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [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 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:
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","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotCash, DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]),0)-NZ(DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# 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 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"
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","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotCash, DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]= " & [ClientCIN]),0)-NZ(DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS Balance
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"
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]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance
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
You have made a good start, but some of your SQL has become really garbled. For example, you have:
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
The DSum function takes three arguments: FieldName, TableName and Criteria. Currently, your TableName is:
"[Clients Transaction(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
which would give you something like this:
"[Clients Transaction(TransactionDate < #2014-01-01#"
Naturally, as there is no table of this name, it results in an error.
What you need is this:
DSum(
"AmountReceived",
"[Clients Transaction]",
"(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
& " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#")
& " AND TransactionID <= " & [TransactionID]
& ")) AND ClientCIN= " & [ClientCIN]
) AS TotCash
It is not strictly necessary to spread it out like this, but it makes it easier to read!
The other DSum expressions have similar (but not identical!) problems.
See if you can apply these principles to correct your SQL by yourself. If not then I'll be glad to help further, but I think you will be more satisfied if you fix it yourself. :-)
By the way, I recommend you try the version using the subquery that I suggested, instead of using DSum.
All the best,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 5 August 2014 21:42
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report Balance on each row should be correct on date order
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
39
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.
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 ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# 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 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:
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 ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [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 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:
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","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotCash, DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]),0)-NZ(DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# 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 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"
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","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotCash, DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]= " & [ClientCIN]),0)-NZ(DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS Balance
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"
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]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance
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
__._,_.___
Posted by: Khalid Tanweer <khalidtanweerburrah@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (18) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar