UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Query Tracking Numbers?, Office 2003    
 
   
crcool75
post Oct 18 2011, 04:01 PM
Post#1



Posts: 98
Joined: 1-October 11



Hello,
I have a order processing database that is also used to keep up with tracking information. Attached is my relationship structure. When generating an invoice report off of Invoice_T and Invoice_Details_T, how can I retrieve the tracking numbers related to each PartNum? The PartNum's between PO_Details_T and Invoice_Details_T are the same, but how would I relate them?
Thanks
Attached File(s)
Attached File  Relationships.jpg ( 89.64K )Number of downloads: 5
 
Go to the top of the page
 
theDBguy
post Oct 18 2011, 04:06 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,339
Joined: 19-June 07
From: SunnySandyEggo


Hi,
There are you storing the Tracking number for each PartNum? Do you have another table where the Shipping information for each part is being tracked?
Go to the top of the page
 
crcool75
post Oct 18 2011, 05:05 PM
Post#3



Posts: 98
Joined: 1-October 11



Yes, it is Shipping_T (refer to relationships image). Tracking numbers are maintained by each individual part within the PO_Details_T. Disregard the Tracking field within Invoice_T.
Go to the top of the page
 
crcool75
post Oct 19 2011, 08:05 PM
Post#4



Posts: 98
Joined: 1-October 11



theDBguy? Did I lose you? iconfused.gif
Go to the top of the page
 
crcool75
post Oct 19 2011, 10:57 PM
Post#5



Posts: 98
Joined: 1-October 11



I have attached my current database. After giving this a lot of thought it seems my relationships will need to change in order create accurate queries. Am I correct? I'm just not sure how to arrange the relationships since the PO's and Invoices are broke apart in two tables each.
Attached File(s)
Attached File  db1.zip ( 327.31K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post Oct 20 2011, 03:57 PM
Post#6


Access Wiki and Forums Moderator
Posts: 75,339
Joined: 19-June 07
From: SunnySandyEggo


Hi,
Since the part number tracking number and invoice info are in separate tables, I'm not sure how you can safely combine the info for your report. You might have to use subreports instead.
Take a look at Query3 in the attached modified version of your db. That's the closest I could get the two together.
Hope that helps...
Attached File(s)
Attached File  db1.zip ( 213.41K )Number of downloads: 2
 
Go to the top of the page
 
crcool75
post Oct 20 2011, 04:51 PM
Post#7



Posts: 98
Joined: 1-October 11



Thanks, theDBguy... You are correct, there's no real safe method of matching the invoice with the PO.
That I have been doing is securing my relationships, linking the PO details to the invoice details and only allowing the user to create invoices off of "already created" PO's. After I finish I will have a foreign record in the invoice details from the PO details. Did I say that correct? This will hopefully allow me to look up shipping information from the invoice details.
Thanks again for your help!
Go to the top of the page
 
theDBguy
post Oct 20 2011, 05:00 PM
Post#8


Access Wiki and Forums Moderator
Posts: 75,339
Joined: 19-June 07
From: SunnySandyEggo


Hi,
Yes, I think if you create the Invoice table as a child of the PODetails table, then you should be able to link the two together.
Good luck with your project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2019 - 02:14 AM