p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Select Query Help (http://p2p.wrox.com/showthread.php?t=30210)

dbartelt June 7th, 2005 04:13 PM

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

penta June 13th, 2005 08:06 AM

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

All times are GMT -4. The time now is 09:08 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.