Two Table Query

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I want to create a query from two tables. The first
table:

Patent No
1Inventor
2Inventor
Title
SerialNo
etc.


The second table is Inventors:

InventorID (AutoNo)
Inventor
InventorAddress
InventorCity
InventorState
InventorZip
InventorPhone
etc.

I want to set the criteria so that if the inventorID=1
(the ID in the second table) then it will pull the
address, etc. from that inventor into the query and if
the InventorID=2, then it will pull the correct
information from the Inventor table. Obviously, I am not
too familiar with expressions etc. Could somebody PLEASE
help me? Thanks in advance.
 
HI:

What field is common to both tables? This is how you link the two.
Whatever field it is just click that field in table 1 and drag it to table 2
in your query design

then add the fields by double clicking them and they get placed below the
tables window into the query columns. No expressions needed unless you want
to make a calculation or filter values from the records.

hit the ! run query button to test the results

design a form or report using this new query as the record source

any help?
 
That is the problem. There is no common field to both
tables. There could be more than one inventor per
record. I want to try and run a query that pulls the
information together by InventorID.

For example:
If in Table 2 (the Inventors table)

InventorID=1 is (John Smith)
InventorID=2 is (Mark Jones)
InventorID=3 is (David Somebody)

Then, in Table 1:

Record #1 - 1Inventor is David Somebody,
2Inventor is John Smith, 3 Inventor is Mark Jones

Record #2 - 1Inventor is John Smith, 2Inventor is Mark
Jones

Record #3 - 1Inventor is David Somebody


The query pull together:

Record #1 - 1Inventor, David Somebody, Address, PhoneNo.
etc.; 2Inventor, John Smith, Address, PhoneNo. etc.

Record #2 - 1Inventor, John Smith, Address, PhoneNo. etc.

Does this make any sense?




The end result of the query would show that InventorID=1,
Name is John Smith, Address is .. . , AND in Table 1 if
John Smith is 2Inventor (the second inventor) it will
show all of his information (from the inventors table) in
a subform. If in another record John Smith is the
1Inventor then all of his information (from the Inventors
table) will show on that record.
 
Hi:

Without a field to link the two tables , I wish you luck. The way you
designed your database, your query on the two tables will cause multiplicity
in the record selection. You should make an ID field unique to both tables
if you want to query relative information.

sorry
 
Back
Top