Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

cross checking columns

Discussion in 'Information Technology' started by Amity, Nov 1, 2005.

  1. Amity

    Amity
    Expand Collapse
    Guest

    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!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News How A Sikh Engineer From Gujarat Developed A Ramadan App That Crossed Half-a-million Breaking News Jul 6, 2016
    Controversial No Gay Marriages, Gurdwaras across World Told Hard Talk Dec 17, 2013
    Controversial Namdhari Nitnem : Crossing The Line? Hard Talk Oct 3, 2013
    Canada Freeman movement grows across Canada Breaking News Sep 3, 2013
    A Shabad I came across - possible interpretations Sikh Sikhi Sikhism Aug 28, 2013

  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    __________________________________________

    "Amity" wrote:

    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!
     
  4. bpeltzer

    bpeltzer
    Expand Collapse
    Guest

    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

    "Amity" wrote:

    > 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!
     
  5. Pat Hartman (MVP)

    Pat Hartman (MVP)
    Expand Collapse
    Guest

    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.

    "Amity" wrote:

    > 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!
     

Share This Page