Query 2 Tables for Addresses from Multiple Processors

  • Thread starter Thread starter ssmbob
  • Start date Start date
S

ssmbob

I am trying to get a Query for a report to list the Processors Used for a
JobNo and the Adresses for them.
I have two Tables with the only thing in Common is The Processor Name.
Table1: COC Table
Fields As Follows ~ All Text:
Primary Key
JobNo Processor1 Processor2 Processor3
S119185 Texas Precision Plating Treffers Precision HAR-CONN Chrome
S119194 In House Hytek Finishes Co.
S121223 Texas Precision Plating

Table2: Processor Table
Types & Fields As Follows:
Primary Key
Text Auto Number Text Text
Processor PRID Street
City-State
Texas Precision Plating 1 2220 Grisham Rowlett, TX 75088
Treffers Precision 2 5000 Augusta Dr. Fort Worth, TX 76106
HAR-CONN Chrome 3 1500 I-35W Denton, TX 76207
Hytek Finishes Co. 4 8127 216 th St. Kent, WA 98032
In House 5 P.O. Box 1 Irving, TX 75017-0186
 
I am trying to get a Query for a report to list the Processors Used for a
JobNo and the Adresses for them.
I have two Tables with the only thing in Common is The Processor Name.
Table1: COC Table
Fields As Follows ~ All Text:
Primary Key
JobNo Processor1 Processor2 Processor3
S119185 Texas Precision Plating Treffers Precision HAR-CONN Chrome
S119194 In House Hytek Finishes Co.
S121223 Texas Precision Plating
Click to expand...

Well... this table is incorrectly designed. You have repeating fields. If each
Job has multiple Processors and each Processor can do multiple jobs (as is
evidently the case) you should really have *three* tables: Jobs; Processors;
and JobProcessor, with the JobNo and the primary key of Processors as its two
fields. Note that a processor name is a BAD choice of foreign key; what if
Table2: Processor Table
Types & Fields As Follows:
Primary Key
Text Auto Number Text Text
Processor PRID Street
City-State
Texas Precision Plating 1 2220 Grisham Rowlett, TX 75088
Treffers Precision 2 5000 Augusta Dr. Fort Worth, TX 76106
HAR-CONN Chrome 3 1500 I-35W Denton, TX 76207
Hytek Finishes Co. 4 8127 216 th St. Kent, WA 98032
In House 5 P.O. Box 1 Irving, TX 75017-0186
Click to expand...

As it stands, you will need to create a query by adding COC to the query grid,
and then adding Processor *three times*. Join Processor1 to Processor in the
first instance, Processor2 to Processor in the second, etc. Select each Join
line and choose Option 2 - "Show all records in COC and matching records in
Processor". But I'd really suggest normalizing your table!
 
I tried that early last week in a COCQry by adding Processor 6 Times as there
actually are up 6 processors, but when I do, a form I use (from a Orders
Table) that Automatically Populates fields such as Customer, Part#, Qty can
be seen stops populating those fileds. Since the addresses are not actually
needed at that point I decided to have two Queries, 1 for info entered and, 1
for a report I need.

Let me Fill You in with more Details.
There are actually 3 Tables Involed in this particular part of the DB.
1) A Linked Table to Orders ~ JobNo as Main Identfier
2) COC Table ~ Info to Be stored in. Field JobNo also in it. and Fields
Proc1, Proc2, etc up to Proc6.
3) Processors Table ~ Just a List of Processors with their addresses.
I have a query ~ COCQry from the Orders Table & COC Table that is
Relationshipped by the JobNo, and A Form from this Query COCFrm. This form is
were info is entered into the COC Table. And Yes This Form also has
Prosessors Combo Boxed Drop Down Lists that is Limited to Lists with Options
to add new Processors. and it all works, but if I try adding Processor 3 or 6
Times in the Query the Form stops Populating the Needed Fields Customer,
Part#, Qty etc.

So I decided to use another Query, COCQry Query, it has the COCQry and
Processors Table in it. and this is where my COC Report (Certificate of
Conformance) will come from.
It has all the info from the COCQry including Proc1, Proc2, etc up to Proc6
and this is where I need to get the processors addresses from for my Report,
but I can not find a way to relationship the Processors used with the
Processors Addresses for my report.

I appreciate your Help so Far, with the added information I hope you will be
able to provide me an answer.
Thank You
ssmbob
 
I have solved the problem. If I can find out how I will close this Thread
Thank You
SSMBOB
 
Back
Top