Query to Show Non Matching records 1

[medreports2]

I am trying to create a query in Access to help identify non-approved database records. I have one table containing the standards [TABLE.STANDARDS] and another table containing records [TABLE.ALLDATA] .

Three fields are used to create a unique approved combination:
FIELD.CATEGORY
FIELD.TYPE
FIELD.KEYWORD1

[TABLE.STANDARDS] includes all approved combinations of these three database fields. What I need to do is show all records contained in [TABLE.ALLDATA] that do not have an matching combination of the 3 records in [TABLE.STANDARDS] so that they can be fixed.

Please help!!

 

[PHV]

SELECT A.*
FROM [TABLE.ALLDATA] A LEFT JOIN [TABLE.STANDARDS] S
ON A.[FIELD.CATEGORY] = S.[FIELD.CATEGORY] AND A.[FIELD.TYPE] = S.[FIELD.TYPE] AND A.[FIELD.KEYWORD1] = S.[FIELD.KEYWORD1]
WHERE S.[FIELD.CATEGORY] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[medreports2]

Maybe I did this wrong, but it seems that the results are coming back as the approved records as opposed to the Non-approved. The example below is based on the actual field names from my SQL query. Anything you can see I did wrong?


SELECT MasterDataTable.*
FROM [MasterDataTable] LEFT JOIN [MED Standards] ON MasterDataTable.Category=[MED Standards].[Category] AND MasterDataTable.Type = [MED Standards].[Type] AND MasterDataTable.SubType1 = [MED Standards].[SubType1]
WHERE [MED Standards].[Category] Is Null

 

[MichaelRed]

there is a simplified approach illustrated in the wizzards which are part of Ms. A. itself. "Unmatched". Right on hte query section of hte db window.




MichaelRed

 

[medreports2]

The first one was actually right. I had an error in one of the tables! Thanks to you both