Query Running Balance
No luck again it sum-up all sales & all purchase for each invoice but I want
a running balance for each invoice separately as
InventoryCode Invoice# Purchase Sale
Balnce
20041 IJKL-001 15
0 15
20041 IJKL-002 0
5 10
20041 IJKL-002 20
5 25
and so on
Further I notify the Inventory movement Extended is a crosstab query not a
table
Regards,
Abdul Shakeel
"vanderghast" wrote:
No luck as ... ?
Note that DSum accepts expression, so, instead of two DSum, you can use just
one (which could be faster):
DSum("[Purchase]","Inventory Movement
Extended","[InvoiceNumber]='" & [InvoiceNumber] &
"'")-DSum("[Sale]","Inventory Movement Extended","[InvoiceNumber]='" &
[InvoiceNumber] & "'") AS Balance
can be changed to
DSum("Nz(Purchase,0) - Nz(Sale,0)","Inventory Movement
Extended","[InvoiceNumber]='" & [InvoiceNumber] &
"'") AS Balance
But what is the problem, exactly? If you have a dateTimeStamp field, maybe
it would be safer to use it, instead of the InvoiceNumber.
Vanderghast, Access MVP
"Abdul Shakeel" wrote in message
...
I am using the following query to create running balance at every change of
Invoice Number but no luck, here Invoice Number is a text field
SELECT [Inventory Movement Extended].InventoryCode, [Inventory Movement
Extended].InvoiceDate, [Inventory Movement Extended].InvoiceNumber,
[Inventory Movement Extended].[Company Name], [Inventory Movement
Extended].InventoryName, [Inventory Movement Extended].Purchase,
[Inventory
Movement Extended].Sale, DSum("[Purchase]","Inventory Movement
Extended","[InvoiceNumber]='" & [InvoiceNumber] &
"'")-DSum("[Sale]","Inventory Movement Extended","[InvoiceNumber]='" &
[InvoiceNumber] & "'") AS Balance
FROM [Inventory Movement Extended];
any suggestions!!!
--
Regards,
Abdul Shakeel
|