Find unmatched records 1

[stocktondesigns]

I've got a couple of tables, tblCustomers and tblProducts. tblCustomers has a unique UserID and the tblProducts includes a unique ProductID - the tblProducts includes a UserID field so the tables maintain a relationship. If a customer registers, but does not select any products, there is no record in tblProducts. Is there a query I can run that will pull up all customers who have no related record in tblProducts?

In Access you can do this using the find Unmatched query wizard, but there is no such query using a SQL Project in Access. Would appreciate any tips on how to run this.

 

[cmmrfrds]

Select * From tblCustomers
Left Join tblProducts
On tblProducts.UserId = tblCustomers.UserID
Where ProductID IS NULL

A left join from the customer to the products table will get all the equal and the ProductID will be Null when there is no matching product record.

 

[uscitizen]

i'm sure this doesn't work....

SELECT [ID Table].[Last Name], [ID Table].[First Name], [ID Table].[MI], [ID Table].[MR Number]
FROM [ID Table] LEFT JOIN [Screening Log] ON [ID Table].[MR Number]=[Screening Log].[MR Number] And
[ID Table].[MI]=[Screening Log].[MI] And
[ID Table].[Last Name]=[Screening Log].[Last Name] And
[ID Table].[First Name]=[Screening Log].[First Name];

what i'm doing is searching for records in 'ID Table' having no matching couterpart in 'Screening Log'. here matching means identically matched on last name, first name, mi, and medical record number.




“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[cmmrfrds]

And [ID Table].[First Name]=[Screening Log].[First Name]
WHERE [Screening Log].[MR Number] IS NULL

after the last join check the screening log table for a null in the [Screening Log].[MR Number]

 

[uscitizen]

hi cmmrfrds,

can you explain to me what the final sql phrase would look like. i'm having trouble from reading your reply just above.
...and why the WHERE [Screening Log].[MR Number] IS NULL
part would do it -- i mean "[Screening Log].[MR Number] IS NULL" is never true!?


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[cmmrfrds]

Because of the left join,
[Screening Log].[MR Number] IS NULL" can be true!

When there is no equal, but there is a record in the ID table then any field in the Log table is null in the query. In fact, that is a common way to check for unmatched records in the log table. If you want to reverse then do a right join and check for null fields in the ID table.

Try the statement!

 

[uscitizen]

hi cmmrfrds,

this sounds like it's bound to work! will definitely put it to the test monday morning. have a good wknd

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

hi,

the query is definitely running if i am a tad confused by the 'left' and 'right' join distinction. maybe it's that cerebral dominance thing. at any rate, let me review briefly the scope of my application so we can agree that we're focusing on it correctly.

there are two forms/tables. the user enters last names, etc etc etc in the "ID Log" and their values become available to the user in the 'Screening Log" via combo boxes which get their values via queries run on the "ID Log". so a name or number that gets entered in "Screening Log" must also be in "ID Log". The purpose of this test is to ensure that if there are any changes to names and/or numbers in the "ID Log" that the user have a way of learning of them and remedy them in the "Screening Log".

is this still the prescription to take?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln