Query to show all records of one table and updated info from anoth

Discussion in 'Microsoft Access Queries' started by Bob Dancer, Jul 18, 2008.

  1. Bob Dancer

    Bob Dancer Guest

    I am setting up a query to get updates on one table (A) of over 1000 records
    from another table (B) of over 90,000 from the Bureau Work Com. The tables
    are joined by BWC policy numbers. The larger table doesn't have all the
    policies the smaller table has. Each time I query I only recieved about 450
    records of Table (A). I want to get all of the updated and unchanged records
    from Table A in one query that can be updated as I add new records to Table
    B. How can I do this?

    --
    Bob
     
    Bob Dancer, Jul 18, 2008
    #1
    1. Advertisements

  2. Bob Dancer wrote:

    >I am setting up a query to get updates on one table (A) of over 1000 records
    >from another table (B) of over 90,000 from the Bureau Work Com. The tables
    >are joined by BWC policy numbers. The larger table doesn't have all the
    >policies the smaller table has. Each time I query I only recieved about 450
    >records of Table (A). I want to get all of the updated and unchanged records
    >from Table A in one query that can be updated as I add new records to Table
    >B.


    Try using an outer join. Maybe something like:

    SELECT tableA.*, tableB.*
    FROM tableA LEFT JOIN tableB
    ON tableA.BWC = tableB.BWC

    The records that don't exist in tableB will be Null in the
    second set of fields.

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jul 18, 2008
    #2
    1. Advertisements

  3. Bob Dancer

    Bob Dancer Guest

    Re: Query to show all records of one table and updated info from a

    Will it still update the information from one table or the other in the Query
    results? For example I am using Table A to update fields such as "Primary
    Name" and "Tax ID" and Table B for "Current Risk Type" and "Coverage Status
    Code". Both tables have each of these fields. Will the query show all the
    records in Table A (those matched with Table 2 and those which did not) and
    include the updates from Table 2?
    --
    Bob


    "Marshall Barton" wrote:

    > Bob Dancer wrote:
    >
    > >I am setting up a query to get updates on one table (A) of over 1000 records
    > >from another table (B) of over 90,000 from the Bureau Work Com. The tables
    > >are joined by BWC policy numbers. The larger table doesn't have all the
    > >policies the smaller table has. Each time I query I only recieved about 450
    > >records of Table (A). I want to get all of the updated and unchanged records
    > >from Table A in one query that can be updated as I add new records to Table
    > >B.

    >
    > Try using an outer join. Maybe something like:
    >
    > SELECT tableA.*, tableB.*
    > FROM tableA LEFT JOIN tableB
    > ON tableA.BWC = tableB.BWC
    >
    > The records that don't exist in tableB will be Null in the
    > second set of fields.
    >
    > --
    > Marsh
    > MVP [MS Access]
    >
     
    Bob Dancer, Jul 18, 2008
    #3
  4. Re: Query to show all records of one table and updated info from a

    Bob Dancer wrote:

    >Will it still update the information from one table or the other in the Query
    >results? For example I am using Table A to update fields such as "Primary
    >Name" and "Tax ID" and Table B for "Current Risk Type" and "Coverage Status
    >Code". Both tables have each of these fields. Will the query show all the
    >records in Table A (those matched with Table 2 and those which did not) and
    >include the updates from Table 2?



    No.

    A Select query only returns the records. You haven't
    explained your goal clearly enough to develop the procedure
    that would be required to do all those things.

    The first thing you need to do is examine the results of
    that query to see if it contains only the records you want
    to work with and that it returns all the data you need to do
    the job (as per your original question).

    If/when the select query presents the needed data, then you
    can start thinking about how to use it to do what you need
    to do. You will probably want to append the new records
    into table B. Then, you can consider what to do with the
    records that already exist in table B and what to do with
    the fields values that are different (update some fields or
    replace the whole record).

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jul 18, 2008
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alex Cowell

    Query not updated when table updated

    Alex Cowell, Oct 31, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    279
    Jeff Boyce
    Nov 3, 2003
  2. Guest
    Replies:
    8
    Views:
    296
    Guest
    Nov 3, 2004
  3. Guest
    Replies:
    3
    Views:
    208
    Guest
    Aug 15, 2006
  4. Guest
    Replies:
    1
    Views:
    295
    Guest
    Mar 15, 2007
  5. Guest
    Replies:
    5
    Views:
    149
    Allen Browne
    Mar 30, 2007
Loading...

Share This Page