Double references in a query

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Rolf Rosenquist" wrote in message
...
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

Rolf Rosenquist wrote:

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.

--
Marsh
MVP [MS Access]

Thanks for the instructions that made it work !
/ Rolf


"Marshall Barton" skrev i meddelandet
...
Rolf Rosenquist wrote:

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.

--
Marsh
MVP [MS Access]