Union query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a query but don't know how to word it.
I have two tables PhoneJacks and People. The PhoneJack table has JackNum,
Bulding, and RoomNum as it's Key Field (the People table contains those
fields as well as other person related fields).

How do I get everything in the PhoneJack table that is not found in the
People table? Everything I have tried has given me wrong results.
 
EdLeeYoung said:
I need a query but don't know how to word it.
I have two tables PhoneJacks and People. The PhoneJack table has JackNum,
Bulding, and RoomNum as it's Key Field (the People table contains those
fields as well as other person related fields).

How do I get everything in the PhoneJack table that is not found in the
People table? Everything I have tried has given me wrong results.
Click to expand...


The Find Unmatch Query Wizard should be able to guide
through creating an appropriate query.
 
The Find Unmatch Query Wizard is no good unless you are comparing one (1)
field in each table. The key field I need to compare is four (4) fields.
 
Build the query with the find unmatched query on one of the fields
Then modify the query by joining on the other fields. The join should be in
the same direction (and same type) as the one that the wizard built.
Now add the IS Null criteria under each of the joining fields in the same
table as the wizard used.

If you can't do that, post the SQL (text view) and tell us which fields you
want to join on and someone should be able to modify the SQL statement for
you.
 
Here's my queries...don't know which is correct. I want every
Jack,building,room combo from my Jack Table that is not listed in my Directory
Query 1 gives me 3924 reponses
Query 2 gives me 4186 reponses
Query 1 contains "WHERE (((Directory.Jack) Is Null))"
Query 2 does not
I don't know whaty "WHERE (((Directory.Jack) Is Null))" does...I saw it
online on a help page.


SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack, Jacks.[Bldg
Num]
FROM Jacks LEFT JOIN Directory ON (Jacks.[Bldg Desc]=Directory.[Bldg Desc])
AND (Jacks.Room=Directory.Room) AND (Jacks.Jack=Directory.Jack)
WHERE (((Directory.Jack) Is Null))
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;


SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack, Jacks.[Bldg
Num]
FROM Jacks LEFT JOIN Directory ON (Jacks.[Bldg Desc]=Directory.[Bldg Desc])
AND (Jacks.Room=Directory.Room) AND (Jacks.Jack=Directory.Jack)
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;
 
The following query should give you every jack that doesn't have a match in
the Directory table - based on Jack,
bldg Desc, and Room being an EXACT match.

SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room,
Jacks.Jack, Jacks.[Bldg Num]
FROM Jacks LEFT JOIN Directory
ON Jacks.[Bldg Desc]=Directory.[Bldg Desc]
AND Jacks.Room=Directory.Room
AND Jacks.Jack=Directory.Jack
WHERE Directory.[Bldg Desc] is Null
AND Directory.Room is Null
AND Directory.Jack Is Null
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;



EdLeeYoung said:
Here's my queries...don't know which is correct. I want every
Jack,building,room combo from my Jack Table that is not listed in my
Directory
Query 1 gives me 3924 reponses
Query 2 gives me 4186 reponses
Query 1 contains "WHERE (((Directory.Jack) Is Null))"
Query 2 does not
I don't know whaty "WHERE (((Directory.Jack) Is Null))" does...I saw it
online on a help page.


SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack, Jacks.[Bldg
Num]
FROM Jacks LEFT JOIN Directory ON (Jacks.[Bldg Desc]=Directory.[Bldg
Desc])
AND (Jacks.Room=Directory.Room) AND (Jacks.Jack=Directory.Jack)
WHERE (((Directory.Jack) Is Null))
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;


SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack, Jacks.[Bldg
Num]
FROM Jacks LEFT JOIN Directory ON (Jacks.[Bldg Desc]=Directory.[Bldg
Desc])
AND (Jacks.Room=Directory.Room) AND (Jacks.Jack=Directory.Jack)
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;
Click to expand...
 
thank you

John Spencer said:
The following query should give you every jack that doesn't have a match in
the Directory table - based on Jack,
bldg Desc, and Room being an EXACT match.

SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room,
Jacks.Jack, Jacks.[Bldg Num]
FROM Jacks LEFT JOIN Directory
ON Jacks.[Bldg Desc]=Directory.[Bldg Desc]
AND Jacks.Room=Directory.Room
AND Jacks.Jack=Directory.Jack
WHERE Directory.[Bldg Desc] is Null
AND Directory.Room is Null
AND Directory.Jack Is Null
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;



EdLeeYoung said:
Here's my queries...don't know which is correct. I want every
Jack,building,room combo from my Jack Table that is not listed in my
Directory
Query 1 gives me 3924 reponses
Query 2 gives me 4186 reponses
Query 1 contains "WHERE (((Directory.Jack) Is Null))"
Query 2 does not
I don't know whaty "WHERE (((Directory.Jack) Is Null))" does...I saw it
online on a help page.


SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack, Jacks.[Bldg
Num]
FROM Jacks LEFT JOIN Directory ON (Jacks.[Bldg Desc]=Directory.[Bldg
Desc])
AND (Jacks.Room=Directory.Room) AND (Jacks.Jack=Directory.Jack)
WHERE (((Directory.Jack) Is Null))
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;


SELECT Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack, Jacks.[Bldg
Num]
FROM Jacks LEFT JOIN Directory ON (Jacks.[Bldg Desc]=Directory.[Bldg
Desc])
AND (Jacks.Room=Directory.Room) AND (Jacks.Jack=Directory.Jack)
ORDER BY Jacks.[Bldg Desc], Jacks.Floor, Jacks.Room, Jacks.Jack;




John Spencer said:
Build the query with the find unmatched query on one of the fields
Then modify the query by joining on the other fields. The join should be
in
the same direction (and same type) as the one that the wizard built.
Now add the IS Null criteria under each of the joining fields in the same
table as the wizard used.

If you can't do that, post the SQL (text view) and tell us which fields
you
want to join on and someone should be able to modify the SQL statement
for
you.

The Find Unmatch Query Wizard is no good unless you are comparing one
(1)
field in each table. The key field I need to compare is four (4)
fields.

:

EdLeeYoung wrote:

I need a query but don't know how to word it.
I have two tables PhoneJacks and People. The PhoneJack table has
JackNum,
Bulding, and RoomNum as it's Key Field (the People table contains
those
fields as well as other person related fields).

How do I get everything in the PhoneJack table that is not found in
the
People table? Everything I have tried has given me wrong results.


The Find Unmatch Query Wizard should be able to guide
through creating an appropriate query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top