Linking data

[joey]

Hello,

I have three tables with the same unique identifier - Account Number

One table has all the records
One table has Cars
One table has Trucks

What I would like to do is run a query that displays the Account Numbers
that have neither Cars nor Trucks.

Thanks in Advance.

 

[Maurice]

Joey,

Create a query from the table with all the records and set parameters in the
fields cars and truck to: is null

Comment: it is not good practice to have a table with all the records. Based
on several joins you should create a query which shows 'all' the records.

Maurice

 

[Dennis]

Create a query in design view with your 3 tables added. Create 2 joins from
your table with all records account number to account number in cars table
and account number in trucks table. Change both these join types to 3 (show
all records from full table and only those from cars and trucks that match).
Put the account number from your full table into the query grid and then the
account numbers from your cars table and your trucks table into the query
grid.
In the criteria row for both the cars and trucks account numbers put
Is Null
Save and run the query

 

[joey]

My problem is that the tables for Cars and Trucks only have the Account
Numbers for Cars and Trucks. I can get a query to show them all but I can't
get the nul function to work. How can I align the records so that I can get
the Account Numbers that don't have an association with either Cars or Trucks.

 

[Maurice]

Have you tried the querywizard "Unrelated records". It's one of the options
in the query template dialog.

Choose new query - from the list choose the appropriate one.

 

[John Spencer]

I believe that a query like the following should work.

SELECT AllRecords
FROM (AllRecords LEFT JOIN Cars
ON AllRecords.[Account Number] = Cars.[Account Number])
LEFT JOIN Trucks ON
AllRecords.[Account Number] = Trucks.[Account Number])
WHERE Cars.[Account Number] is Null and Trucks.[Account Number] Is Null

You were given instructions on how to build this query in the grid. Did you
try it? Did it fail? If it failed, how did it fail? Did you get the wrong
records, no records, an error message? You have to tell us, we cannot see
what happens, you can.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[joey]

Sorry for the delay. The Unrelated Records worked Perfectly!!!

Thanks so much!

Joey

I believe that a query like the following should work.

SELECT AllRecords
FROM (AllRecords LEFT JOIN Cars
ON AllRecords.[Account Number] = Cars.[Account Number])
LEFT JOIN Trucks ON
AllRecords.[Account Number] = Trucks.[Account Number])
WHERE Cars.[Account Number] is Null and Trucks.[Account Number] Is Null

You were given instructions on how to build this query in the grid. Did you
try it? Did it fail? If it failed, how did it fail? Did you get the wrong
records, no records, an error message? You have to tell us, we cannot see
what happens, you can.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have three tables with the same unique identifier - Account Number

One table has all the records
One table has Cars
One table has Trucks

What I would like to do is run a query that displays the Account Numbers
that have neither Cars nor Trucks.

Thanks in Advance.