SQL Select Statement for Quantity On Hand (1 Viewer)

[medihub_guy]

Good day,

I'm trying to calculate quantity on hand (QOH) for a drug that has 2 batch numbers. The query constructed in the Access query builder shows the correct information. However, when constructing the SQL statement for VBA, it shows the wrong values. Here is the query in SQL view from the access query builder:

SELECT [GenericNameStrength_childdrug] & " " & [Abbreviation_form] AS DrugNameStrengthForm, ChildDrug_T.GenericNameStrength_childdrug, Form_T.Abbreviation_form, ChildDrug_T.BatchNumber_childdrug, ChildDrug_T.ExpirationDate_childdrug, Nz([SumOfQuantity_goodsin])-Nz([SumOfQuantity_goodsout]) AS QOH, StockIn_Q.LocationID, ChildDrug_T.ParentDrugID, ChildDrug_T.PackageSize_childdrug, ChildDrug_T.UnitCost_childdrug, ChildDrug_T.Active_childdrug, Supplier_T.CompanyName_supplier, ChildDrug_T.InvoiceNumber_goodsin, ChildDrug_T.Date_goodsin, Manufacturer_T.CompanyName_manufacturer, OrderType_T.OrderType_description, ChildDrug_T.ChildDrugID, ChildDrug_T.OrderTypeID
FROM (((Form_T INNER JOIN (((StockOut_Q RIGHT JOIN ChildDrug_T ON StockOut_Q.ChildDrugID = ChildDrug_T.ChildDrugID) LEFT JOIN StockIn_Q ON ChildDrug_T.ChildDrugID = StockIn_Q.ChildDrugID) INNER JOIN ParentDrug_T ON ChildDrug_T.ParentDrugID = ParentDrug_T.ParentDrugID) ON Form_T.FormID = ParentDrug_T.FormID) INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID) INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID) INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID
WHERE (((StockIn_Q.LocationID)=[Forms]![3-General_SplashScreen_F]![LocationID]) AND ((ChildDrug_T.ParentDrugID)=[forms]![Pharmacy_ParentDrugList_ChildDrug_Edit_F]![ParentDrugID]));

I understand that Nz is not a function recognized by SQL. So, I wrote this in my VBA editor:

Function ChildDrug_Edit_Q()

Dim db As Database
Dim qd As QueryDef
Dim qr As String

On Error Resume Next
Set db = CurrentDb
On Error GoTo 0

CurrentDb.QueryDefs("PassThrough_Q").SQL = "SELECT *, " _
& "(Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form)) As DrugNameStrengthForm, " _
& "StockIn_Q.LocationID, PriceTable_T.MarkUp_pricetable, PriceTable_T.TaxRate_pricetable, " _
& "PriceTable_T.DispensingFee_pricetable, Supplier_T.CompanyName_supplier, OrderType_T.OrderType_description, StockIn_Q.[SumOfQuantity_goodsin], StockOut_Q.[SumOfQuantity_goodsout], " _
& "(IsNull([SumOfQuantity_goodsin],0)- IsNull([SumOfQuantity_goodsout],0)) AS QOH " _
& "FROM ChildDrug_T " _
& "INNER JOIN (SELECT GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID, Sum(GoodsOut_T.Quantity_goodsout) AS SumOfQuantity_goodsout FROM GoodsOut_T WHERE GoodsOut_T.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & " GROUP BY GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID) AS StockOut_Q ON StockOut_Q.ChildDrugID = ChildDrug_T.ChildDrugID " _
& "INNER JOIN (SELECT GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID, Sum(GoodsIn_T.Quantity_goodsin) AS SumOfQuantity_goodsin FROM GoodsIn_T WHERE GoodsIn_T.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & " GROUP BY GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID) AS StockIn_Q ON StockIn_Q.ChildDrugID = ChildDrug_T.ChildDrugID " & "INNER JOIN ParentDrug_T ON ParentDrug_T.ParentDrugID = ChildDrug_T.ParentDrugID " _
& "INNER JOIN Form_T ON ParentDrug_T.FormID = Form_T.FormID " _
& "INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID " _
& "INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID " _
& "INNER JOIN PriceTable_T ON PriceTable_T.PriceTableID = ChildDrug_T.PriceTableID " _
& "INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID " _
& "WHERE Stockin_Q.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & "" _
& "AND ChildDrug_T.Active_childdrug = '-1' " _
& "AND ChildDrug_T.ParentDrugID =" & Forms!Pharmacy_ParentDrugList_ChildDrug_Edit_F!Subform!ParentDrugID & "" _
& "ORDER BY ChildDrug_T.ExpirationDate_childdrug"

End Function

Any advice?

Stephen

 

[CJ_London]

Looks to me like the issue is select * in your pass through query

 

[medihub_guy]

hey CJ_London.. thanks for the reply. heres an updated query where Im getting the same result:


CurrentDb.QueryDefs("PassThrough_Q").SQL = "SELECT " _
& "Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form) As DrugNameStrengthForm, " _
& "OrderType_T.OrderType_description, Manufacturer_T.CompanyName_manufacturer, ChildDrug_T.BatchNumber_childdrug, " _
& "ChildDrug_T.ExpirationDate_childdrug, ChildDrug_T.ManufacturingDate_childdrug, ChildDrug_T.PackageSize_childdrug, " _
& "ChildDrug_T.UnitCost_childdrug, ChildDrug_T.InvoiceNumber_goodsin, ChildDrug_T.Date_goodsin, ChildDrug_T.Image_childdrug, " _
& "(StockIn_Q.[SumOfQuantity_goodsin]) AS StockIn, " _
& "(StockOut_Q.[SumOfQuantity_goodsout]) AS StockOut, " _
& "StockIn_Q.LocationID, Supplier_T.CompanyName_supplier " _
& "FROM (SELECT GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID, Sum(GoodsIn_T.Quantity_goodsin) AS SumOfQuantity_goodsin FROM GoodsIn_T GROUP BY GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID) As StockIn_Q " _
& "RIGHT JOIN (SELECT GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID, Sum(GoodsOut_T.Quantity_goodsout) AS SumOfQuantity_goodsout FROM GoodsOut_T GROUP BY GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID) As StockOut_Q ON StockOut_Q.ChildDrugID = StockIn_Q.ChildDrugID " _
& "INNER JOIN ChildDrug_T ON StockIn_Q.ChildDrugID = ChildDrug_T.ChildDrugID " _
& "INNER JOIN ParentDrug_T ON ChildDrug_T.ParentDrugID = ParentDrug_T.ParentDrugID " _
& "INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID " _
& "INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID " _
& "INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID " _
& "INNER JOIN Staff_T ON ChildDrug_T.StaffID = Staff_T.StaffID " _
& "INNER JOIN Form_T ON ParentDrug_T.FormID = Form_T.FormID " _
& "WHERE StockIn_Q.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & "" _
& "AND ChildDrug_T.ParentDrugID =" & Forms!Pharmacy_ParentDrugList_ChildDrug_Edit_F!Subform!ParentDrugID & "" _
& "AND ChildDrug_T.Active_childdrug = '-1' " _
& "ORDER BY ChildDrug_T.ExpirationDate_childdrug"

 

[Josef P.]

Do [SumOfQuantity_goodsin] and [SumOfQuantity_goodsout] show the correct value and only the difference is wrong?

Problem finding recommendation:

Dim SqlText as String
SqlText = "SELECT " _
               & "Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form) As DrugNameStrengthForm, " _
               & ....

Debug.Print SqlText
STOP ' Copy SQL from immediate window to SQL Server Management Studio and test it.
CurrentDb.QueryDefs("PassThrough_Q").SQL = SqlText

One question: Why don't you create a view (with joins, without where) for this in the SQL server and then only use

Select * from YourSchema.YourView where LocationID = ....

BTW:

Active_childdrug = '-1'

You use a text data field for that?

If you make the SQL text more readable, I'll be happy to try to understand the logic behind it. Currently applies: lim(pleasure of reading) => 0

 

[medihub_guy]

Do [SumOfQuantity_goodsin] and [SumOfQuantity_goodsout] show the correct value and only the difference is wrong?

Problem finding recommendation:

Dim SqlText as String
SqlText = "SELECT " _
               & "Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form) As DrugNameStrengthForm, " _
               & ....

Debug.Print SqlText
STOP ' Copy SQL from immediate window to SQL Server Management Studio and test it.
CurrentDb.QueryDefs("PassThrough_Q").SQL = SqlText

One question: Why don't you create a view (with joins, without where) for this in the SQL server and then only use

Select * from YourSchema.YourView where LocationID = ....

BTW:

You use a text data field for that?

If you make the SQL text more readable, I'll be happy to try to understand the logic behind it. Currently applies: lim(pleasure of reading) => 0

I'll take you up one that... Here's the query pulled from MS access query builder when you select "SQL View":


SELECT OrderType_T.OrderType_description, [GenericNameStrength_childdrug] & " " & [Abbreviation_form] AS GenericNameStrengthForm, Manufacturer_T.CompanyName_manufacturer, ChildDrug_T.BatchNumber_childdrug, ChildDrug_T.ExpirationDate_childdrug, ChildDrug_T.ManufacturingDate_childdrug, ChildDrug_T.PackageSize_childdrug, ChildDrug_T.UnitCost_childdrug, ChildDrug_T.InvoiceNumber_goodsin, ChildDrug_T.Date_goodsin, ChildDrug_T.Image_childdrug, Nz([SumOfQuantity_goodsin],0) AS StockIn, Nz([SumOfQuantity_goodsout],0) AS StockOut, [StockIn]-[StockOut] AS QOH, StockIn_Q.LocationID, Supplier_T.CompanyName_supplier
FROM ((((Form_T INNER JOIN (((StockOut_Q RIGHT JOIN StockIn_Q ON StockOut_Q.ChildDrugID = StockIn_Q.ChildDrugID) INNER JOIN ChildDrug_T ON StockIn_Q.ChildDrugID = ChildDrug_T.ChildDrugID) INNER JOIN ParentDrug_T ON ChildDrug_T.ParentDrugID = ParentDrug_T.ParentDrugID) ON Form_T.FormID = ParentDrug_T.FormID) INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID) INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID) INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID) INNER JOIN Staff_T ON ChildDrug_T.StaffID = Staff_T.StaffID
WHERE (((StockIn_Q.LocationID)=[Forms]![3-General_SplashScreen_F]![LocationID]))
ORDER BY [GenericNameStrength_childdrug] & " " & [Abbreviation_form];

 

[CJ_London]

also ask why the correct result in access sql is all inner joins, whilst the T-SQL has a right join? and you seem to have dropped your isnull's

 

[medihub_guy]

The above works perfectly on the form.

As for the SQL formatted query in VBA editor: I narrowed the error down to when the QOH is calculated.

First, I had to replace the Nz function with IsNull.. i.e. IsNull([SumOfQuantity_goodsout],0) AS StockOut.

I also isolated the [SumOfQuantity_goodsout] and for some reason this is the same as [SumOfQuantity_goodsin].

I cant figure this one out. Driving me crazy!

 

[medihub_guy]

also ask why the correct result in access sql is all inner joins, whilst the T-SQL has a right join? and you seem to have dropped your isnull's

I mimicked what I retrieved in SQL view from the ms access query builder as much as possible. The tricky part are the subqueries StockIn_Q and StockOut_Q. Everything else on the form shows but this is where im getting my error.

 

[Minty]

I agree @Josef P.
Stick this in a view without the where clause, then create a filtered query on that in access.

If you post the debug.print of your SQL string we can easily help with the sub queries.

 

[medihub_guy]

Not sure if I executed it correctly. I got nothing.
But from what I see when then pass-through query is generated, if the stock out is null then it equals the stock in for some strange reason.

 

[Pat Hartman]

Is there some problem with executing the Access version of the query that makes you want to use a pass-through? Use the tools in SSMS to watch the traffic so you can see what query Access is actually sending to the server. You might be able to capture that SQL if Access sends it in one string. Access does make every effort to "pass through" all queries. It doesn't always succeed so in some cases, you might need actual pass-through queries if you are doing something that Access can't translate well. Also, with all the joins, you might be able to create a middle ground where you link to a server-side view that handles some of the joins. That can also speed up "Access" queries so you don't have to recreate them. Pass-through queries are not updateable and so they interfere with the use of bound forms.

 

[cheekybuddha]

More readable:

SELECT 
  ot.OrderType_description, 
  [GenericNameStrength_childdrug] & " " & [Abbreviation_form] AS GenericNameStrengthForm, 
  m.CompanyName_manufacturer, 
  cd.BatchNumber_childdrug, 
  cd.ExpirationDate_childdrug, 
  cd.ManufacturingDate_childdrug, 
  cd.PackageSize_childdrug, 
  cd.UnitCost_childdrug, 
  cd.InvoiceNumber_goodsin, 
  cd.Date_goodsin, 
  cd.Image_childdrug, 
  Nz([SumOfQuantity_goodsin],0) AS StockIn, 
  Nz([SumOfQuantity_goodsout],0) AS StockOut, 
  [StockIn]-[StockOut] AS QOH, 
  si.LocationID, 
  s.CompanyName_supplier
FROM (
  (
    (
      (
        Form_T f
        INNER JOIN (
          (
            (
              StockOut_Q so
              RIGHT JOIN StockIn_Q si
                      ON so.ChildDrugID = si.ChildDrugID
            ) 
            INNER JOIN ChildDrug_T cd
                    ON si.ChildDrugID = cd.ChildDrugID
          ) 
          INNER JOIN ParentDrug_T pd
                  ON cd.ParentDrugID = pd.ParentDrugID
        ) 
                ON f.FormID = pd.FormID
      ) 
      INNER JOIN Manufacturer_T m
              ON cd.ManufacturerID = m.ManufacturerID
    ) 
    INNER JOIN OrderType_T ot
            ON cd.OrderTypeID = ot.OrderTypeID
  ) 
  INNER JOIN Supplier_T s
          ON cd.SupplierID = s.SupplierID
) 
INNER JOIN Staff_T st
        ON cd.StaffID = st.StaffID
WHERE si.LocationID = [Forms]![3-General_SplashScreen_F]![LocationID]
ORDER BY 
  [GenericNameStrength_childdrug] & " " & [Abbreviation_form]
;
/CODE]

 

[cheekybuddha]

Some questions:

1. Why is table Staff_T in this query? It's not used anywhere in the SELECT, WHERE or ORDER BY
2. What does table Form_T do in this query? I guess field Abbreviation_form is from that table.
3. If this is a pass-through query, why are you using the silly Access SQL nested joins syntax which make understanding your query much more confusing?
4 Are fields GenericNameStrength_childdrug and Abbreviation_form indexed? (They should be if you are using them for ORDER BY - but you will not be able to use the indices if you use the concatenated expression)

 

[medihub_guy]

Some questions:

1. Why is table Staff_T in this query? It's not used anywhere in the SELECT, WHERE or ORDER BY
2. What does table Form_T do in this query? I guess field Abbreviation_form is from that table.
3. If this is a pass-through query, why are you using the silly Access SQL nested joins syntax which make understanding your query much more confusing?
4 Are fields GenericNameStrength_childdrug and Abbreviation_form indexed? (They should be if you are using them for ORDER BY - but you will not be able to use the indices if you use the concatenated expression)

Hi there CheekyBudda,

1. Yes I'm supposed to add a concatenated statement to show which staff member is responsible for the transaction.
2. Correct. Abbreviation_form is from table Form_Y.
3. I did not use the above statement as a pass-through query. I couldn't get the pass-through query to work properly. So copied and pasted the sql view version of the query I built in access of which works however a pass-through query will have better performance.
4. No they are not indexed so I can concatenate the fields.

 

[medihub_guy]

Is there some problem with executing the Access version of the query that makes you want to use a pass-through? Use the tools in SSMS to watch the traffic so you can see what query Access is actually sending to the server. You might be able to capture that SQL if Access sends it in one string. Access does make every effort to "pass through" all queries. It doesn't always succeed so in some cases, you might need actual pass-through queries if you are doing something that Access can't translate well. Also, with all the joins, you might be able to create a middle ground where you link to a server-side view that handles some of the joins. That can also speed up "Access" queries so you don't have to recreate them. Pass-through queries are not updateable and so they interfere with the use of bound forms.

The only reason I'm not using the query built in access is due to performance. I am currently waiting 10-20 seconds for the form to open to display the results of which is unacceptable.
You are spot on with the sql view route. I've created both subqueries in sql view and have joined them to created another view to merge both subqueries. I will then filter this as a pass-through query in vba using form values. I got interrupted with trying this out and will head home soon to continue. Many thanks to everyone offering advice! I really appreciate you guys!

 

[medihub_guy]

So I did it!

Function QOHComp_Q()

Dim db As Database
Dim qd As QueryDef
Dim qr As String

On Error Resume Next
Set db = CurrentDb
On Error GoTo 0
CurrentDb.QueryDefs("PassThrough_Q").SQL = "SELECT " _
& "Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form) As DrugNameStrengthForm, " _
& "StockIn_Q.LocationID, StockIn_Q.ChildDrugID, (IsNull(SumOfQuantity_goodsin, 0) - IsNull(SumOfQuantity_goodsout,0)) As QOH " _
& "FROM " _
& "(SELECT " _
& "GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID, Sum(GoodsIn_T.Quantity_goodsin) AS SumOfQuantity_goodsin " _
& "FROM GoodsIn_T " _
& "GROUP BY GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID " _
& "HAVING GoodsIn_T.LocationID=" & [Forms]![3-General_SplashScreen_F]![LocationID] & ") AS StockIn_Q " _
& "LEFT JOIN " _
& "(SELECT " _
& "GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID, Sum(GoodsOut_T.Quantity_goodsout) AS SumOfQuantity_goodsout " _
& "FROM GoodsOut_T " _
& "GROUP BY GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID " _
& "HAVING GoodsOut_T.LocationID=" & [Forms]![3-General_SplashScreen_F]![LocationID] & ") AS StockOut_Q " _
& "ON StockOut_Q.ChildDrugID = StockIn_Q.ChildDrugID " _
& "INNER JOIN ChildDrug_T ON StockIn_Q.ChildDrugID = ChildDrug_T.ChildDrugID " _
& "INNER JOIN ParentDrug_T ON ChildDrug_T.ParentDrugID = ParentDrug_T.ParentDrugID " _
& "INNER JOIN Form_T ON Form_T.FormID = ParentDrug_T.FormID " _
& "ORDER BY ChildDrug_T.GenericNameStrength_childdrug"

End Function

Works like a charm! Hopefully this helps someone!

 

[CJ_London]

4. No they are not indexed so I can concatenate the fields.

Maybe I am misunderstanding your response but indexing has nothing to do with concatenation. CB's point is a concatenated field is not indexed

 

[medihub_guy]

I understand indexing will help performance of the query by helping access find the record quicker. Tbh I don't know what on earth I was thinking.

 

[Pat Hartman]

Also, you need to change the Having to a Where. Access makes this mistake when building totals queries. Unless you actually specify Where, Access assumes Having. The query engine isn't smart enough to work out which is the right choice. You need to tell it.

WHERE is applied Before the data is aggregated and will use indexes if they are available. You should always use it for criteria that applies to non-aggregated columns. It could dramatically reduce the number of rows that need to be joined or aggregated because it eliminates them up front.
HAVING is applied After the data is aggregated and should ONLY be used against aggregated data such as Sums and never uses indexes.

Some queries will use both Where and Having. Others will use one or the other.

Select ... From ...
Where Location = Forms!yourform!yourfield
Group by ...
Having Sum(....) > 1000

Also, try this change on your Access query just to see if it makes a difference there. I haven't examined queries being sent to the server for quite some time and I don't know if this Access mistake influences Access to send bad queries to the server.