SELECT tblNewStockData.[Item Code], SUM(tblNewStockData.Quantity) AS QuantityRecieved, SUM(tblIssueData.Quantity) AS QuantityIssued, (SUM(tblNewStockData.Quantity) - SUM(tblIssueData.Quantity)) AS Balance
from tblNewStockData, tblIssueData
GROUP by tblNewStockData.[Item Code];
Thanks for the help Hans. There's a bit of confusion in calculation.
I have 4 rows of data in tblNewStockData where the sum of apples in quantity column is 13 but the quantityreceived column in query table is showing 26 as balance. I have two rows of data in quantity column of tblIssueData where the sum is 2 apples. but the qunatityissued column is showing balance as 8. what might have been done wrong in the calculation steps?
It's because the tables aren't joined. The way to do this is to create separate totals queries for items received and items issued, then create a query joining them to a products/items table or query to calculate the balance.
Stock.zip
You do not have the required permissions to view the files attached to this post.
Sub ID()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set ws = Worksheets("results")
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=D:\V\Database.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT[Item Code], [Description], FROM qryBalance;"
rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
ws.Range("A1").CopyFromRecordset rst
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
But I get the error Undefined function Nz in expression. Is there a way I can get through this?
strSQL = "SELECT qryProducts.[Item Code], qryProducts.Description, qryNewStockData.QuantityReceived, qryIssueData.QuantityIssued, [QuantityReceived]-[QuantityIssued] AS Balance FROM (qryProducts LEFT JOIN qryNewStockData ON qryProducts.[Item Code] = qryNewStockData.[Item Code]) LEFT JOIN qryIssueData ON qryProducts.[Item Code] = qryIssueData.[Item Code];"
However for orange since there is no amount taken value in tblIssueData the QuantityIssued column in excel has no value and therefore the balance column for orange has now value. is this because I have removed the zeros for the query>
Nz is specific to Microsoft Access. ADODB does not recognize it. Change the SQL of qryBalance to
SELECT qryProducts.[Item Code], qryProducts.Description, qryNewStockData.QuantityReceived, qryIssueData.QuantityIssued, IIf(IsNull([QuantityReceived]),0,[QuantityReceived])-IIf(IsNull([QuantityIssued]),0,[QuantityIssued]) AS Balance
FROM (qryProducts LEFT JOIN qryNewStockData ON qryProducts.[Item Code] = qryNewStockData.[Item Code]) LEFT JOIN qryIssueData ON qryProducts.[Item Code] = qryIssueData.[Item Code];
Thanks for the help Hans. It worked fine. I have a products table where it has two columns named Max and Min. How can I get these two columns in the qry balance so that these two columns data get copied with the above query.
should it be with SELECT qryProducts.[Item Code], qryProducts.Description, Products[Max], Products[Min]?
Here's how I've done. it doesn't work
SELECT tblProducts.[Item Code], tblProducts.Description, tblProducts.Min, tblProducts.Max, qryNewStockData.QuantityReceived, qryIssueData.QuantityIssued, Nz([QuantityReceived],0)-Nz([QuantityIssued],0) AS Balance
FROM (qryProducts LEFT JOIN qryNewStockData ON qryProducts.[Item Code] = qryNewStockData.[Item Code]) LEFT JOIN qryIssueData ON qryProducts.[Item Code] = qryIssueData.[Item Code];
This version seems to work.
SELECT tblProducts.[Item Code], tblProducts.Description, tblProducts.Min, tblProducts.Max, qryNewStockData.QuantityReceived, qryIssueData.QuantityIssued, Nz([QuantityReceived],0)-Nz([QuantityIssued],0) AS Balance
FROM (tblProducts LEFT JOIN qryNewStockData ON tblProducts.[Item Code] = qryNewStockData.[Item Code]) LEFT JOIN qryIssueData ON tblProducts.[Item Code] = qryIssueData.[Item Code];