Query Relationship\Multiple Join

[Carol Giannini]

I'm trying to do what was described in this previous post and the related
answer:

*****

The problem arises when I use query data from the combined tables to merge
into a Word document. A section of this document list the tenant employee's
vehicle information in columns for "Vehicle 1" and "Vehicle 2". Since the
construct of the document uses the vehicle's particulars as inserted data
fields, when the employee has one vehicle, the merge document prints the
information perfectly. If the employee has two vehicles, the document
prints the same vehicle info twice (once in each "Vehicle" column) instead
of the two different vehicles' particulars output in each of the columns.

You may want to consider a query joining the Vehicle table to the
Tenant table TWICE: just add two instances of Vehicle. Use a criterion
(such as Vehicle_1.VIN > Vehicle.VIN) to ensure that the two instances
refer to different vehicles; use an Outer Join for both instances to
still display tenant data whether they have zero, one, or two
vehicles.

You can then use Vehicle.Make as the source for one the "Vehicle 1"
merge fields, and Vehicle_1.Make as the source for "Vehicle 2".

John W. Vinson[MVP]
*****

I need to do something similar. My first table contains clients, each with
a unique client number. The second table contains up to two related records
for each client. The first table is linked to the second on the client
number, one to many; each record in the second table has an ID number,
auto-assigned so that the second record for each client always has a number
greater than the first.

Because I have to use Word merge documents because of other office needs,
I'm essentially trying to create a query that, for a selected client, will
convert the related records from a vertical format to one horizontal record,
if that makes sense.

I've joined the second table to the first table twice, using a left outer
join, with criteria Record_1.ID > Record.ID. This works great *IF* a client
has two entries in the second table. Queries on clients with no entries or
one entry don't work. Can this be done this way? What am I missing? TIA.

 

[John Vinson]

I've joined the second table to the first table twice, using a left outer
join, with criteria Record_1.ID > Record.ID. This works great *IF* a client
has two entries in the second table. Queries on clients with no entries or
one entry don't work. Can this be done this way? What am I missing? TIA.

If Record_1.ID might not exist, you need to allow for the possibility
that it is NULL instead of greater than Record.ID:

Record_1.ID > Record.ID OR Record_1.ID IS NULL


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Carol Giannini]

John - I've included the Or Is Null in the expression for Record_1.ID, and
the query still shows only the clients that have two records. I don't know
the answer to this question: Will Is Null include records that don't exist,
or does there have to be a record with nothing in the specified field? In my
project, the second table will never include records where the ID is null;
there will simply be no second record, and I'm wondering if that's the
problem. Thanks *very much* for your help. I'd really like to find a
solution to this issue; it will make a huge difference in the project I'm
working on.

 

[John Vinson]

John - I've included the Or Is Null in the expression for Record_1.ID, and
the query still shows only the clients that have two records. I don't know
the answer to this question: Will Is Null include records that don't exist,
or does there have to be a record with nothing in the specified field? In my
project, the second table will never include records where the ID is null;
there will simply be no second record, and I'm wondering if that's the
problem. Thanks *very much* for your help. I'd really like to find a
solution to this issue; it will make a huge difference in the project I'm
working on.

Hrm. Should work if you have Left Joins to both instances of the
table, and no additional criteria. Could you open the query in SQL
view and post it here?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Carol Giannini]

Here it is:

SELECT Client.[No], Client.Name, Witness.Wit_No, Witness.Name,
Witness_1.Wit_No, Witness_1.Name
FROM (Client LEFT JOIN Witness ON Client.[No] = Witness.Client_No) LEFT JOIN
Witness AS Witness_1 ON Client.[No] = Witness_1.Client_No
WHERE (((Witness_1.Wit_No)>[Witness].[Wit_No] Or (Witness_1.Wit_No) Is Null));

 

[John Vinson]

Here it is:

Not sure why it's not working! If there is one witness, you should get
that witness in Witness_Name; if there are no witnesses at all you
won't get the client. A Left Join will fill in NULL values for all
fields in the joined table if there is no matching record, so the Is
Null criterion should handle the case where there is no record in
Witness_1. I'll ask some of the other query gurus what might be wrong.

SELECT Client.[No], Client.Name, Witness.Wit_No, Witness.Name,
Witness_1.Wit_No, Witness_1.Name
FROM (Client LEFT JOIN Witness ON Client.[No] = Witness.Client_No)
LEFT JOIN Witness AS Witness_1 ON Client.[No] = Witness_1.Client_No
WHERE (((Witness_1.Wit_No)>[Witness].[Wit_No] Or (Witness_1.Wit_No) Is
Null));


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps