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

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

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