Results 1 to 3 of 3

Thread: How do I?

  1. #1
    Join Date
    Jul 2002

    Talking Unanswered: How do I?

    Does anyone know how to write a query that will tell me which fields are left remaining, in a comparison between a master table and a lesser table which contains the same field (account number)?
    The lesser table gradually fills up with the account numbers that are on the master as visits to the sites are completed.
    I am using MS Access for this.
    I can't remember how to do this.
    My brain has turned to cheese.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select stuff from master
    where not exists
    (select 1 from lesser
    where acctnum=master.acctnum)

    you could also try

    select stuff from master
    select stuff from lesser

    which is standard sql, but i have no idea if access supports this yet -- access 97 didn't


  3. #3
    Join Date
    Aug 2002
    Kampala, Uganda - East Africa
    Use this:

    SELECT master.fieldx
    FROM master
    WHERE master.fieldx NOT IN
    (SELECT corresponding_field_in_lesser_table
    FROM lesser)

    Make sure that u are reffering to the same field from both tables. As in, may be u should use the account numbers field from both tables.

    The 1st SELECT statement picks records that are not in the 2nd SELECT statement.
    The 2nd SELECT statement picks up all the data from the lesser table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts