Double references in a query

R

Rolf Rosenquist

I have one table for vendors, one for customers and one for countries among
others. The two first have a field "countryNo" and the forms have references
to the country table via combo boxes for registering the addresses. It works
as intended for both tables.

In a query I want to get the fields for both the vendors and the customers
addresses together with orders. The addresses are written one by one and
they show up as expected, but when I use the country table to get the
countries I cannot get them properly.

Is there a way to tell the query that with the vendors country number it
should write the vendors country and with the customers country number show
the customers country?

/ Rolf
 
D

Douglas J. Steele

You need to refer to the country table twice in your query, joining one
instance to Vendor, and the other to Customer.
 
M

Marshall Barton

Rolf said:
I have one table for vendors, one for customers and one for countries among
others. The two first have a field "countryNo" and the forms have references
to the country table via combo boxes for registering the addresses. It works
as intended for both tables.

In a query I want to get the fields for both the vendors and the customers
addresses together with orders. The addresses are written one by one and
they show up as expected, but when I use the country table to get the
countries I cannot get them properly.

Is there a way to tell the query that with the vendors country number it
should write the vendors country and with the customers country number show
the customers country?


Add the Countries table to the query twice. The second copy
will be named something like Countries_1.

Now, drag the vendor countryNo field to its counterpart in
the Countries table and drag customers table's countryNo
field to its counterpart in the Countries_1 table.

Make usre there are no extraneous connecting lines left over
from Access' attempts to make this automatic.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top