UNMATCHED QUERY 1

[Tmat]

I am trying to create a unmatched query using the wizard but it only allows me to match up one field. In my case the Primary Key is three fields. I have tried to add some fields in the SQL but its not working. Any help would be appreciated.
Thanks Tmat

 

[MichaelRed]

An approach is to create queries w/ the "primarykey" as fields in the queries. Each query is - obviously - based on one of the tables you are using in the un-matched "wizzard". Do the UnMatched Wizzard on the two (new?) queries using the (new?) PromaryKey fields.

Crude - but effective.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Tmat]

I am not sure what you mean? I tried creating two queries but I am still running into the same problem. I must be missing somthing?
Tmat

 

[MichaelRed]

Thanks for your help but, I am not sure if I understand. and I apologize in advance if this is inappropriate of me to write to you and understand if you do not reply. This is my situation I have two tables one I pull the information to create, the other is sent to me from the company I need to match them up and also show unmatched. The unique key is PropID,Doc#,Item#

I tried to create separate queries as you said but I just get the same situation?

tblRec -- Note Name changed
PropertyID
Doc#
Item#
Description

tblShip -- Note Name changed
PropertyID
Doc#
Item#
Description


Using the above definitions,

qryRecPriKey

SELECT tblRec.*, [PropertyId] & [docNum] & [ItemNum] AS PriKey
FROM tblRec;

qryShipPriKey

SELECT tblShip.*, [PropertyId] & [docNum] & [ItemNum] AS PriKey
FROM tblShip;

UnNamed query to find the "Unmatched"

SELECT DISTINCTROW [qryRecPriKey].[PropertyID], [qryRecPriKey].[docNum], [qryRecPriKey].[ItemNum], [qryRecPriKey].[Desc], [qryRecPriKey].[PriKey]
FROM qryRecPriKey LEFT JOIN qryShipPriKey ON [qryRecPriKey].[PriKey] = [qryShipPriKey].[PriKey]
WHERE ([qryShipPriKey].[PriKey] Is Null);

The only 'trick' here is the generation in the frst two queries "qryXXXPriKey" of the field [PriKey] and its use in the third query as the "Matching" item/field. There are other ways to do this which are perhaps more 'elegant' - however this is, perhaps, easier to follow and certainly accomplishes the goal.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Tmat]

Thanks for the Example Would have never of got it other wise. Thanks so much for the help.
tmat