Basic SQL question

[eussias]

I'm relatively new to the world of SQL and need some help. What I'm hoping to do... I have 2 tables, both of which have a common field ClientNumber. What I'm hoping to do is write a query which will display all the clients who haven't currently got a related record in their table. It's a one to many realtionship. The main table has 951 Clients, and the other table has 144, therefore the query will result in 807 ClientNumbers... can anyone help??

 

[nickjar2]

Try one of the query wizards. There should be a 'No match' or something similar.

Nick

 

[CosmoKramer]

Look in Access help for the Find Unmatched Query Wizard

 

[eussias]

My machine has Office 2000 Professional installed on it. Unfortunately when you try to run the unmatched query wizard, it asks for the Office 2000 Premium CD, which I do not have, and my workplace isn't willing to purchase. Is there any way around this, or do I need to write the raw sql code?

 

[nickjar2]

I think you may have to write the SQL yourself. I cannot think of the syntax sorry. All i can remember is there will be an outer join and is null somewhere. Someonw here is bound to give you the syntax though.

Nick

 

[CosmoKramer]

The syntax would be something like this:

SELECT DISTINCTROW Table1.Num, Table1.LastName, Table1.FirstName
FROM Table1 LEFT JOIN Table2 ON Table1.Num = Table2.Num
WHERE (((Table2.PersonNum) Is Null));

 

[CosmoKramer]

Sorry, a typo:

SELECT DISTINCTROW Table1.Num, Table1.LastName, Table1.FirstName
FROM Table1 LEFT JOIN Table2 ON Table1.Num = Table2.Num
WHERE (((Table2.Num) Is Null));

 

[eussias]

Thanks guys.. working perfectly now