Re: Union query
- From: "EdLeeYoung" <EdLeeYoung@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 16 Feb 2006 05:53:28 -0800
thank you
"John Spencer" wrote:
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" <EdLeeYoung@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C94E8AFF-4F63-4105-97C0-BB29303FA964@xxxxxxxxxxxxxxxx
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" wrote:
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.
"EdLeeYoung" <EdLeeYoung@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0466AF4C-BCD8-4674-B3A5-791226C1AB95@xxxxxxxxxxxxxxxx
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.
"Marshall Barton" wrote:
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.
--
Marsh
MVP [MS Access]
- References:
- Re: Union query
- From: Marshall Barton
- Re: Union query
- From: John Spencer
- Re: Union query
- From: EdLeeYoung
- Re: Union query
- From: John Spencer
- Re: Union query
- Prev by Date: Re: How do I compute yesterday's business date if today is Monday
- Next by Date: RE: I am receiving an #Error in my query even after using Nz funct
- Previous by thread: Re: Union query
- Next by thread: Re: Expression for multi-purpose field
- Index(es):
Relevant Pages
|
