[AccessD] SQL question (wich item isn't in the table?)

SD,

This is just a wild @$$ suggestion, but since you prefer not use the
"unmatched" query method using a second table of search values, perhaps it
is possible to replicate the effect of such a query using only one table.
I'm referring to a past thread on in-line sub queries and the use of
temporary or virtual tables.  You may be able to utilize the []. syntax to
achieve your desired results...?  ...and to really put it all together, is
it possible to make it a parameter query that prompts you for your search
item to be placed in the temp table?  Please let me know if you have any
success with this.



Mark


-----Original Message-----
From: Mitsules, Mark S. (Newport News) [mailto:mitsules_ms at nns.com] 
Sent: Wednesday, August 13, 2003 8:26 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] SQL question (wich item isn't in the table?)


SD,

Create a table "UnmatchedClientID" listing your search items.


SELECT UnmatchedClientID.ID
FROM UnmatchedClientID LEFT JOIN ClientID ON UnmatchedClientID.ID =
ClientID.ID WHERE (((ClientID.ID) Is Null));


Mark


-----Original Message-----
From: Sad Der [mailto:accessd666 at yahoo.com] 
Sent: Wednesday, August 13, 2003 7:40 AM
To: Acces User Group
Subject: [AccessD] SQL question (wich item isn't in the table?)


Hi,

can anybody help me with this. 
I've got this query:
SELECT DISTINCT cid.ID 
FROM ClientID cid
WHERE cid.ID IN ('156','157')

Model of table ClientID:
(ID number(3))
Data of table ClientID:
ID
157
158
159

It shows all id's from the where clause that are in
the table, wich is 157.

How do I modify it so that it now shows all id's that
are not in the table (156)

thnx

SD

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com _______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com