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.