I am trying to create an Inventory Tracking form that will pull all open “JobNumber” data for a selected customer from tblJobs. I use a Check Box as the filter: Closed - True / False. I have created a query, “qryInventoryTrackingOpenJobs”, which performs as it should. It only pulls the JobNumbers for a specific Customer that have not yet been “Closed”.

I also need to pull data for shipped orders from tblShippingTag. I am going to use 3 filters, a “Begin Date”, “End Date” and “Customer” to filter “tblShippingTag” for recent shipments. tblShippingTag is set up to accept partial shipments. It will generate a new “ShippingTagID”, which is the PK, and a “TagNumber”, which is sequential + 1. The query below shows duplicate “ShippingTagID” numbers erratically. It is a hit and miss situation. I have scanned “tblShippingTag” for and duplicate “ShippingTagID” numbers and have not found any. There are duplicate “JobNumbers” due to the fact there are partial shipments.

Select Query - qryInventoryTrackingShippingTag

SELECT DISTINCTROW tblShippingTag.JobNumber, tblJob.JobNumber, tblShippingTag.ShippingTagID, tblShippingTag.Customer, tblShippingTag.PONumber, tblShippingTag.DDate, tblShippingTag.Quantity, tblQuantityType.QuantityType, tblShippingTag.PartNumber, tblShippingTag.TagNumber, tblShippingTag.Printed, tblShippingTag.LotNumber, tblJob.JobEntryDate
FROM tblQuantityType INNER JOIN (tblJob INNER JOIN tblShippingTag ON tblJob.JobNumber = tblShippingTag.JobNumber) ON tblQuantityType.QuantityTypeID = tblShippingTag.QuantityTypeID
ORDER BY tblShippingTag.JobNumber;

_:(__

I would also appreciate help with writing code to subtract the “tblShippingTag.Quantity” from “tblJob.Quantity” to show the Open Quantity. Please remember that there may be more than 1 record per “JobNumber” in “tblShippingTag” due to partial shipments.

Thanks in advance for any help.


D. Bartelt

__________________
D. Bartelt

Hi Bartelt,
First part of ur topic i'll let to some expert to help u. Second part i'll
suggest that u read Chap 9 A2K2 Core Reference by Rick Dobson MicrosoftPress
which will have the code for updating inventory quantity of the Northwind db.
With little alterations u should be able to use it for ur Open Qty.
rgds Penta