Query Running Balance

A

Abdul Shakeel

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!!!
 
V

vanderghast

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
 
A

Abdul Shakeel

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 said:
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 said:
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!!!
 
V

vanderghast

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 said:
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 said:
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 said:
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!!!
 
A

Abdul Shakeel

Such a nice person are you, all your suggestion works great thanks alot
--
Regards,

Abdul Shakeel


vanderghast said:
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 said:
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 said:
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

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!!!
 
C

chiyyoor

i've the same dbt
here my table

SL:ID:DISC:DEBI:CREDIT
1 :1 :xxxx:100 :0
2 :2 :xxxx: 50 :0
3 :1 :xxxx: 0 :50
5 :1 :xxxx:100 :0
6 :2 :xxxx: 0 :50

I would like create a query based on the table,the output should show the result as

SL:ID:DISC:DEBI:CREDIT:BALANCE:MOD
1 :1 :xxxx:100 : 0: 100:Dr
2 :1 :xxxx: 0 : 50: 50:Dr
3 :1 :xxxx:100 : 0: 150:Dr

SL:ID:DISC:DEBI:CREDIT:BALANCE:MOD
1 :2 :xxxx: 50 : 0: 50:Dr
2 :2 :xxxx: 0 : 50: 00:Dr

any possibilities..
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
?

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
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
 
J

John Spencer

You can try the following UNTESTED SQL.

SELECT 1 + Count(B.SL) as NewSL
, A.ID, A.Disc, A.Debi, A.Credit
, (A.Credit + Sub(B.Credit)) - (A.Debi + Sum(B.Debi)) as Balance
, IIF(A.Debi>0,"Db",IIF(A.Credit>0,"Cr",Null)) as Mod
FROM [SomeTable] as A LEFT JOIN [SomeTable] as B
ON A.ID = B.ID
AND A.SL > B.SL
GROUP BY A.ID
, A.Disc
, A.Debi, A.Credit
, IIF(A.Debi>0,"Db",IIF(A.Credit>0,"Cr",Null))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top