STUCK ON SQL FOR QUERY 1

[PEASLET]

Hi, I'm a bit stuck; I have a table of clients & I have another table of Client IDs which I do not want to be included in a promotion.
All I want to do is make another table which excludes the second table. In the ContactsID criteria field, I have been trying statements like:
WHERE [Client Details2].[ContactsID] NOT LIKE [Other Table].[ContactsID]

But obviously have it completely wrong as I'm not getting anywhere!!
Can anyone help ....please as I need this done before I go away later on today.

 

[AccessSQLUser]

Try this:

Select [ClientDetail2].[ContactsID] From [ClientDetails2] Left Join [Other Table] on [ClientDetails2].[ContactsID] = [Other Table].[ContactsID] WHERE [Other Table].[ContactsID] Is Null

 

[PEASLET]

Thanks for your speedy response - I've tried it & and have an 'incorrect subquery syntax, enclose subquery in parentheses' error - any ideas as to where this could be going wrong?

 

[AccessSQLUser]

Did you try exactly what I posted or does your query include other syntax too? If it does, please post your whole query?

 

[PEASLET]

On further examination, I have found that if I put brackets around it, it seems to work although I then get a 'no record' error. I think this may be something to do with the 'is null' clause as none of the contactsID fields are null. Is there any other clause I could replace it with that would mean 'not the same as'?

 

[PEASLET]

It is this:

(Select [Client Details2].[ContactsID] From [Client Details2] Left Join [Growth Cos ContactsID] on [Client Details2].[ContactsID] = [Growth Cos ContactsID].[ContactsID] WHERE [Growth Cos ContactsID].[ContactsID] Is Null)

Just pasted from yours with table names changed

 

[THoey]

Try:

Select [Client Details2].[ContactsID]
From [Client Details2]
WHERE [Client Details2].[ContactsID] NOT IN
(
SELECT [Growth Cos ContactsID].[ContactsID]
FROM [Growth Cos ContactsID]
) Terry M. Hoey

 

[PEASLET]

It looks right Terry, but I've got the syntax...parantheses error again...

 

[PEASLET]

Now I have put some more brackets in (at the beginning and at the end) and it is bringing up the error:

'A subquery of this kind cannot return more than one record. Revise the SELECT statement of the subquery to request only one record.'

Hmmm.... what do you think?

 

[tempclerk]

Why don't you try your Find Unmatched query wizard and look at the SQL it produces?

Just a thought.

 

[PEASLET]

By the way, I've done it .... Find unmatched query wizard!!!
Thanks for your help