SQL: Trying to get thats Data Not in Subquery?!

[DanSeal]

Hi,

I have a query in MSAccess. What i'm trying to do is return a result set that contains the data from the Master query that is NOT present in the sub-query. In Pseudo-SQL its something like:

SELECT *
FROM tblClients
WHERE (entryDate between (getUnBookedStart() and getUnBookedEnd())) and
(tblClients.index NOT IN (SELECT tblClients.index, tblBookinds.clientID FROM tblClients, tblBookings WHERE tblClients.index = tblBookings.clientID) ;

I'm stuck as to how to achieve this - any ideas? (If what i'm getting at doesnt make sense i'll try and make it clearer...)

Thanks,

Danny

 

[ssecca]

From what I see what you have looks good except for two things

SELECT *
FROM tblClients
WHERE (entryDate between (getUnBookedStart() and getUnBookedEnd())) and
(tblClients.index NOT IN (SELECT tblClients.index FROM tblClients, tblBookings WHERE tblClients.index = tblBookings.clientID));

An imbeded select can only have one field in the select portion of the select statement and two You where missing a closing peren. I think this will work for you now. Give it a try. I'll check back later gotta catch the bus now.

good luck
ssecca