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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|