jimjennings
asked on
Refining a query for optimized speed
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)
Inventory (tblTransactions)
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:
Me.TransUPC.Requery
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.[Shipp ing Status])="Invoice Printed") AND ((tblCASalesOrders.ItemCan celled)=No ));
*****
2) qryTransQtyAvail relates tblItemDetails with four queries, qryTransInSum, qryTransInSumRO, qryTransOutSum, qryTransSumAdj on UPC
*****
SELECT tblItemDetails.UPC, qryTransInSum.SumOfTransQt y, qryTransInSumRO.SumOfTrans Qty, qryTransOutSum.SumOfTransQ ty, qryTransSumAdj.SumOfTransQ ty, tblItemDetails.[Inventory Number], (Nz([qryTransInSum.SumOfTr ansQty],0) +Nz([qryTr ansInSumRO .SumOfTran sQty],0)+N z([qryTran sSumAdj.Su mOfTransQt y],0)-Nz([ qryTransOu tSum.SumOf TransQty], 0)) AS QtyAvail, tblItemDetails.discontinue d
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.discontin ued)=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.TransQ ty) AS SumOfTransQty
FROM tblTransactions
GROUP BY tblTransactions.TransUPC, tblTransactions.TransType
HAVING (((tblTransactions.TransTy pe)="R"));
*****
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,
Jim Jennings
Sales Orders (imported & appended to tblCASalesOrders)
Purchase Orders (requirements created based on daily Sales orders - tblPurchaseOrders & tblPurchaseOrderDetails)
Inventory (tblTransactions)
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:
Me.TransUPC.Requery
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.[Shipp
*****
2) qryTransQtyAvail relates tblItemDetails with four queries, qryTransInSum, qryTransInSumRO, qryTransOutSum, qryTransSumAdj on UPC
*****
SELECT tblItemDetails.UPC, qryTransInSum.SumOfTransQt
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.discontin
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,
FROM tblTransactions
GROUP BY tblTransactions.TransUPC, tblTransactions.TransType
HAVING (((tblTransactions.TransTy
*****
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,
Jim Jennings
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.