A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query Running Balance



 
 
Thread Tools Display Modes
  #1  
Old May 31st, 2010, 11:28 AM posted to microsoft.public.access.queries
Abdul Shakeel[_2_]
external usenet poster
 
Posts: 99
Default Query Running Balance

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
Ads
  #2  
Old May 31st, 2010, 12:35 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Query Running Balance

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


  #3  
Old May 31st, 2010, 01:00 PM posted to microsoft.public.access.queries
Abdul Shakeel[_2_]
external usenet poster
 
Posts: 99
Default 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


  #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



  #5  
Old May 31st, 2010, 02:33 PM posted to microsoft.public.access.queries
Abdul Shakeel[_2_]
external usenet poster
 
Posts: 99
Default Query Running Balance

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

Abdul Shakeel


"vanderghast" wrote:

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.