cross checking columns

[Amity]

My database holds information gathered from sheep over several years. I have
two large tables holding the relevant information. One I call the 'measured'
data and one is 'visual' data. Basically both tables have animal ID as the
first field. Each animal has a unique identifying number. In the measured
table there is one record per animal so the ID column is a primary key. In
the visual table however, several 'visual recordings' were done per animal so
there may be between 1 and 4 records per ID. I do know that not every animal
in the 'measured' table necessarily has some or any records in the 'visual'
table but I am pretty sure that every animal ID that shows up in the 'visual'
table DOES have a corresponding 'measured' record. (hope I haven't lost you
yet). What I would like to do is create a table/ querie with 'measured ID'
next to 'visual ID' to work out which animals are missing data. I have
worked out so far how to get a total 'visual ID' tag list without duplicates
but if I create a querie linking the visual with measured IDs it will only
give me a list of those animals which have both. i would like a table with
two columns and a eg blank in the 'visual ID' if the 'measured ID' doesn't
match one so I can identify that particular animal.
If you need further clarification I won't be surprised!

 

[Tom Wickerath]

Hi Amity,

but if I create a querie linking the visual with measured IDs it will only
give me a list of those animals which have both.

That's because the join is the default Inner Join. Double-click on the join
line in query design view. Experiment with the two other choices.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

My database holds information gathered from sheep over several years. I have
two large tables holding the relevant information. One I call the 'measured'
data and one is 'visual' data. Basically both tables have animal ID as the
first field. Each animal has a unique identifying number. In the measured
table there is one record per animal so the ID column is a primary key. In
the visual table however, several 'visual recordings' were done per animal so
there may be between 1 and 4 records per ID. I do know that not every animal
in the 'measured' table necessarily has some or any records in the 'visual'
table but I am pretty sure that every animal ID that shows up in the 'visual'
table DOES have a corresponding 'measured' record. (hope I haven't lost you
yet). What I would like to do is create a table/ querie with 'measured ID'
next to 'visual ID' to work out which animals are missing data. I have
worked out so far how to get a total 'visual ID' tag list without duplicates
but if I create a querie linking the visual with measured IDs it will only
give me a list of those animals which have both. i would like a table with
two columns and a eg blank in the 'visual ID' if the 'measured ID' doesn't
match one so I can identify that particular animal.
If you need further clarification I won't be surprised!

 

[bpeltzer]

In this situation, I typically create a UNION query to generate all the
primary keys (ex: SELECT Source1.KeyA as Keys FROM Source1 GROUP BY
Source1.KeyA
UNION SELECT Source2.KeyA FROM Source2, then create another query that
starts with the result of the union query, and performs left joins from that
to each of the input tables (ex: SELECT [Source - All Keys].Keys,
Source1.Result, Source2.Date, Source2.Result FROM ([Source - All Keys] LEFT
JOIN Source1 ON [Source - All Keys].Keys = Source1.KeyA) LEFT JOIN Source2 ON
[Source - All Keys].Keys = Source2.KeyA.
--Bruce

 

[Pat Hartman (MVP)]

Access includes a special query type to help solve this problem. Click on
the new query button and choose the "unmatched" query. The wizard will help
you build it. You should go through the process twice, once from each side
so you can find out where the "missing" records are.