Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 7th, 2005, 04:13 PM
Authorized User
Join Date: Apr 2005
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
Old June 13th, 2005, 08:06 AM
Friend of Wrox
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Query gregalb SQL Server 2000 2 November 15th, 2007 10:56 PM
SELECT from SELECT query? seananderson Access 1 October 12th, 2007 12:40 AM
Need help on select query arul1984 SQL Server 2000 2 July 4th, 2007 01:49 AM
Select query help minhtri SQL Server 2000 4 March 28th, 2005 06:59 PM
select query collie SQL Server 2000 2 January 17th, 2005 03:13 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.