Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 7th, 2005, 04:13 PM
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
  #2 (permalink)  
Old June 13th, 2005, 08:06 AM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:10 PM.


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