Khalid-
Try this:
SELECT [Petty Cash].TransactionID, [Petty Cash].TransactionDate, [Petty Cash].Details, [Petty Cash].AmountReceived,
[Petty Cash].Expenses, DSum("AmountReceived","Petty Cash","TransactionID <=" & [TransactionID]) As TotCash,
DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID]) As TotExpense,
DSum("AmountReceived","Petty Cash","TransactionID <=" & [TransactionID])-DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID]) AS Balance
FROM [Petty Cash]
ORDER BY TransactionID;
Balance should simply be the difference between TotCash and TotExpense. Let me know if either of the two new fields don't show up.
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
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Saturday, October 26, 2013 5:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Balance not updating
John,
Only Balance field is missing the data. I did checked for entering in "AmountReceived" field and also in "Expenses" field both are giving data.
Khalid
On Saturday, October 26, 2013 6:17 PM, John Viescas <JohnV@msn.com> wrote:
Khalid-
You might be getting a "blank" if either of the two DSum functions returns a Null value. Are there any missing values in either AmountReceived or Petty Cash fields?
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
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Saturday, October 26, 2013 8:26 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Balance not updating
Hi All,
"Balan
I have a form "Petty Cash Book" its Record Source is:
SELECT [Petty Cash].TransactionID, [Petty Cash].TransactionDate, [Petty Cash].Details, [Petty Cash].AmountReceived, [Petty Cash].Expenses, DSum("AmountReceived","Petty Cash","TransactionID <=" & [TransactionID])-DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID]) AS Balance
FROM [Petty Cash]
ORDER BY TransactionID;
Couple of days before i deleted some records manually, so now "TransactionID" field which is Auto Number is missing its sequence i.e. after TransactionID:131 i have TransactionID:157 and so on.
Today viewing the report for Petty Cash Book/Table "Petty Cash" i observed that field "Balance" is blank after TransactionID:131.
I can manually fill the Balance field, but i think this is not the right approach, so how could i manage it, and also for in future if i do manually delete any record this thing would again happen.
What is the solution for this OR am i bound not to delete any record manually?
Help for this would be highly appreciated with thanks.
Khalid
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar