Refining a query for optimized speed
Posted on 2011-03-03
Brief background of our inventory/sales/shipping system:
Sales Orders (imported & appended to tblCASalesOrders)
Purchase Orders (requirements created based on daily Sales orders - tblPurchaseOrders & tblPurchaseOrderDetails)
Items (Data relevant to an item, including UPC - tblItems & tblItemDetails)
tblTransactions contains TransID, TransUPC, and TransType along with some other fields
There are four transaction types:
R - Received, S - Shipped, T - Returned, A - Adjusted
The problem (bottleneck) is occurring when shipping orders. In the Ship Orders form, the users scan the order number barcode which pulls up the order (in less than a second). They then click on the UPC field and start scanning the barcode(s). They can scan all items for that order and all will eventually enter, however it takes around 7-10 seconds for each scan to enter as Access is 'calculating ...'
What the form is doing:
When the user clicks on the UPC field (TransUPC), it triggers 'On Got Focus' event which says:
I do this because I want to make sure the item being scanned is relevant to that order and that the item is in inventory (in case it was not scanned in at the Purchase Order receiving stage).
It's the query behind this that I believe is causing the bottleneck. The field TransUPC is a combo box that joins two queries:
1) qryRcvSOUPC relates tblCASalesOrders and tblItemDetails on SKU
SELECT tblCASalesOrders.[order id], tblItemDetails.upc
FROM tblCASalesOrders INNER JOIN tblItemDetails ON tblCASalesOrders.SKU = tblItemDetails.[Inventory Number]
WHERE (((tblCASalesOrders.[Shipping Status])="Invoice Printed") AND ((tblCASalesOrders.ItemCancelled)=No));
2) qryTransQtyAvail relates tblItemDetails with four queries, qryTransInSum, qryTransInSumRO, qryTransOutSum, qryTransSumAdj on UPC
SELECT tblItemDetails.UPC, qryTransInSum.SumOfTransQty, qryTransInSumRO.SumOfTransQty, qryTransOutSum.SumOfTransQty, qryTransSumAdj.SumOfTransQty, tblItemDetails.[Inventory Number], (Nz([qryTransInSum.SumOfTransQty],0)+Nz([qryTransInSumRO.SumOfTransQty],0)+Nz([qryTransSumAdj.SumOfTransQty],0)-Nz([qryTransOutSum.SumOfTransQty],0)) AS QtyAvail, tblItemDetails.discontinued
FROM (((tblItemDetails LEFT JOIN qryTransInSum ON tblItemDetails.UPC = qryTransInSum.TransUPC) LEFT JOIN qryTransOutSum ON tblItemDetails.UPC = qryTransOutSum.TransUPC) LEFT JOIN qryTransInSumRO ON tblItemDetails.UPC = qryTransInSumRO.TransUPC) LEFT JOIN qryTransSumAdj ON tblItemDetails.UPC = qryTransSumAdj.TransUPC
WHERE (((tblItemDetails.UPC) Is Not Null) AND ((tblItemDetails.discontinued)=No))
ORDER BY tblItemDetails.[Inventory Number];
The four queries are all the same, only differing by the TransType (R,S,T,A)
SELECT tblTransactions.TransUPC, tblTransactions.TransType, Sum(tblTransactions.TransQty) AS SumOfTransQty
GROUP BY tblTransactions.TransUPC, tblTransactions.TransType
Basically, i'm taking all current UPC's, summing each TransType and then calculating a total for each UPC.
Is there a better way to do this? A more efficient query?
Thank-you and my apologies if I explained too much,