View Single Post
  #4  
Old May 31st, 2010, 01:57 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Query Running Balance

Can you try:


SELECT InventoryCode,
InvoiceDate,
InvoiceNumber,
[Company Name],
InventoryName,
Purchase,
Sale,
DSum("Nz(Purchase,0) - Nz(Sale, 0)",
"Inventory Movement Extended",
"InventoryCode=" & InventoryCode & " AND [InvoiceNumber]='" &
[InvoiceNumber] & "'") AS Balance

FROM [Inventory Movement Extended]

ORDER BY InventoryCode,
InvoiceDate




or, instead of using DSum, using a subquery. and using the invoice date to
define the order (rather than the invoice number code):


SELECT InventoryCode,
InvoiceDate,
InvoiceNumber,
[Company Name],
InventoryName,
Purchase,
Sale,
(SELECT SUM(Nz(b.Purchase,0)-Nz(b.Sale,0))
FROM [Inventory Movement Extended] AS b
WHERE b.inventoryCode = a.inventoryCode
AND b.invoiceDate = a.invoiceDate ) AS balance

FROM [Inventory Movement Extended] AS a

ORDER BY InventoryCode,
InvoiceDate




or a join:



SELECT a.InventoryCode,
a.InvoiceDate,
LAST(a.InvoiceNumber),
LAST(a.[Company Name]),
LAST(a.InventoryName),
LAST(a.Purchase),
LAST(a.Sale),
SUM(Nz(b.Purchase,0)-Nz(b.Sale,0)) AS balance

FROM [Inventory Movement Extended] AS a
INNER JOIN [Inventory Movement Extended] AS b
ON b.inventoryCode = a.inventoryCode
AND b.invoiceDate = a.invoiceDate

GROUP BY a.InventoryCode,
a.InvoiceDate

ORDER BY a.InventoryCode,
a.InvoiceDate




which should be faster than using DSum.



Vanderghast, Access MVP



"Abdul Shakeel" wrote in message
...
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