Unmatched Query

[Dog17]

Good morning,

I am trying to create a query to find unmatching data. I have data from two different systems I want to compare to find and show the differences.

Example:

System 1

Dog Name.....State.....Status.....Owner.....Gender
Charlie..........MA........Missing....Jacob......M
Jack..............CA........Missing....Shane.....M
Matt..............TX........Found......Adam......F
Tom..............FL.........Found.....Ryan......M
Victor............VA.........Found....John.......M

System 2

Dog Name.....State.....Status.....Owner.....Gender
Charlie..........MA........Missing....Jacob......M
Jack..............CA........Missing....Shane.....M
Matt..............TX........Found......Adam......F
Tom..............FL........ Missing...Ryan......M
Victor............VA........Missing...John.......M

Current SQL:

SELECT [System 1].[Dog Name], [System 1].Status, [System 2].Status, [System 1].State, [System 2].State, [System 1].Gender, [System 2].Gender


FROM [System 1] LEFT JOIN [System 2] ON ([System 1].Status = [System 2].Status) AND ([System 1].[Dog Name] = [System 2].[Dog Name])
WHERE ((([System 2].[Dog Name]) Is Null));

Current Query:

Dog Name.....System 1.Status.....System 2.Status.....System 1.State.....System 2.State.....System 1.Gender.....System 2. Gender
Tom..............Found............................ ...................FL............................. ......................M........................... .......................
Victor............Found........................... ....................VA............................ .......................M.......................... ........................


How do I get the query to display the data for system 2 aswell, like this:

Dog Name.....System 1.Status.....System 2.Status.....System 1.State.....System 2.State.....System 1.Gender.....System 2. Gender
Tom..............Found...................Missing.. ................FL.......................FL....... .................M..........................M..... ..................
Victor............Found...................Missing. .................VA......................VA....... .................M..........................M..... ..................


Thank you for any help!!

[lfpm062010]

If the Dog Name is unique on both table, all you need to LEFT JOIN on the Dog Name and get your information on both table.

If not, then you have to provide more information on table (Primary Key, Field Name, etc).

If possible to post your database, it will be even easier.

[Dog17]

The dog name is the primary key in both tables. Where do I LEFT JOIN?

SELECT [System 1].[Dog Name], [System 2].[Dog Name], [System 1].Status, [System 2].Status, [System 1].State, [System 2].State, [System 1].Gender, [System 2].Gender
FROM [System 1] LEFT JOIN [System 2] ON ([System 1].[Dog Name] = [System 2].[Dog Name]) AND ([System 1].Status = [System 2].Status)
WHERE ((([System 2].[Dog Name]) Is Null));

[lfpm062010]

Code not tested.

SELECT [System 1].[Dog Name], [System 2].[Dog Name], [System 1].Status, [System 2].Status, [System 1].State, [System 2].State, [System 1].Gender, [System 2].Gender
FROM [System 1] LEFT JOIN [System 2] ON ([System 1].[Dog Name] = [System 2].[Dog Name])
;

This should give all record in table [System 1] and give NULL value for non matched in table [System 2].