View Single Post
  #1 (permalink)  
Old June 7th, 2005, 04:13 PM
dbartelt dbartelt is offline
Authorized User
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Query Help

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
Reply With Quote