Query with three tables

[joey]

I posted earlier and was able to pull a query using Unrelated Records but now
I need further assistance.

I have three tables:

One has 15,000 records of account numbers and names in two different fields
One has 8000 records with some of the same account numbers and names plus
Cars as a third field
1One has 4000 records with some of the same acount numbers and names plus
Trucks as the third field

So all Account numbers and names are used for the same three tables.

What I need is a report/query withe the Account Numbers and Names of people
that have not bought a car and the same for people who have not bought a
truck.

I tried creating joins,relationshipsand adding Is Null but all I get either
no records displaying or the 15,000.

Can you please assist me with simplied yet detailed instructions? Thank You

 

[John W. Vinson]

I posted earlier and was able to pull a query using Unrelated Records but now
I need further assistance.

I have three tables:

One has 15,000 records of account numbers and names in two different fields
One has 8000 records with some of the same account numbers and names plus
Cars as a third field
1One has 4000 records with some of the same acount numbers and names plus
Trucks as the third field

So all Account numbers and names are used for the same three tables.

What I need is a report/query withe the Account Numbers and Names of people
that have not bought a car and the same for people who have not bought a
truck.

I tried creating joins,relationshipsand adding Is Null but all I get either
no records displaying or the 15,000.

Can you please assist me with simplied yet detailed instructions? Thank You

No, because you did not provide enough information: your table names and field
names. We cannot see your database!

Have you tried the "Unmatched Query Wizard" on the new query screen? That's
just what it's designed to do. If that's not working for you please post back
with the table and relevant fieldnames.

John W. Vinson [MVP]

 

[joey]

Here Goes:

Table 1 fields
All Account Numbers
All Account Names

Table 2 fields:
Account Numbers
Account Names
Cars (not yes or no)

Table 3 fields
Account Numbers
Account Names
Trucks (not yes or no)

Unmatched Query Wizard results show me the account numbers, names and Cars
or Trucks (like a "Yes" Result)

I am looking for "orphan" Account Numbers and Account Names that are not
associated with Cars/Trucks.

Could the problem be that only Table 1 has all the Account Numbers/Account
Names?

I am stuck!

 

[John W. Vinson]

Here Goes:

Table 1 fields
All Account Numbers
All Account Names

Table 2 fields:
Account Numbers
Account Names
Cars (not yes or no)

Table 3 fields
Account Numbers
Account Names
Trucks (not yes or no)

Unmatched Query Wizard results show me the account numbers, names and Cars
or Trucks (like a "Yes" Result)

I am looking for "orphan" Account Numbers and Account Names that are not
associated with Cars/Trucks.

Could the problem be that only Table 1 has all the Account Numbers/Account
Names?

I think the biggest problem is that Table 2 and Table 3 are incorrectly
designed. You're essentially storing data - the existance of cars or trucks -
in the table definition, rather than storing it as data! What is the
*meaning* of these tables? What does it mean to have a record in Table2 with
Cars set to Yes? What does it mean to have Cars set to No? Why would you store
the account name (redundantly!) in both related tables, since you can find
them by linking with the Account Number in Table1? Why not just have one
table, with a yes/no field for Cars and another for Trucks?

You can find all records in Table1 which have no matching account number in
either Table2 or Table3 using a pair of Subqueries, but I think you may do
better to properly normalize your data!

Try

SELECT * FROM [Table 1]
WHERE NOT EXISTS(SELECT [Account Number] FROM [Table 2] WHERE [Table
2].[Account Number]=[Table 1].[Account Number])
OR NOT EXISTS(SELECT [Account Number] FROM [Table 3] WHERE [Table 3].[Account
Number]=[Table 1].[Account Number]);