Stock balance Query

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Stock balance Query

Post by adam »

Hi anyone,
I'm trying to create a query using the following to create a stock balance.

I'm getting syntax error missing operator in query expression SUM(tblNewStockData.Quantity) QuantityRecieved

Any help on this would be kindly appreciated.

Code: Select all

SELECT tblNewStockData.[Item Code], SUM(tblNewStockData.Quantity) QuantityRecieved, SUM(tblIssueData.Quantity) QuantityIssued, (SUM(tblNewStockData.Quantity) - SUM(tblIssueData.Quantity)) Balance
from tblNewStockData, tblIssueData
GROUP by tblNewStockData.[Item Code];
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

You must use AS before the alias name in Access:

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];

P.S. it should be Received instead of Recieved
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

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?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

Please attach the database (zipped)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

I've attached the file for your reference.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

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.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

I dont know if this the right forum. However I'm trying to get the qryBalance data to excel using the following code.

Code: Select all

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?
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

I've changed the query in excel vba as follows and it works.

Code: Select all

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>
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

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];
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

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]?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

You'd have to add the products table to the query.
If you can't do that yourself, please attach the database again.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

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];
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

See my previous reply.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

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];
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stock balance Query

Post by HansV »

If you want to use the query in Excel, using ADODB, you'll have to replace Nz with an IIf / IsNull combination as indicated above.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Stock balance Query

Post by adam »

it worked with the following query in excel. Thanks for the help Hans.

Code: Select all

strSQL = "SELECT tblProducts.[Item Code], tblProducts.Description, qryNewStockData.QuantityReceived, qryIssueData.QuantityIssued, IIf(IsNull([QuantityReceived]),0,[QuantityReceived])-IIf(IsNull([QuantityIssued]),0,[QuantityIssued])AS Balance, tblProducts.Min,tblProducts.Max FROM (tblProducts LEFT JOIN qryNewStockData ON tblProducts.[Item Code] = qryNewStockData.[Item Code]) LEFT JOIN qryIssueData ON tblProducts.[Item Code] = qryIssueData.[Item Code];"
Best Regards,
Adam