PC Review


Reply
Thread Tools Rate Thread

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

 
 
Bob Dancer
Guest
Posts: n/a
 
      18th Jul 2008
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
 
Reply With Quote
 
 
 
 
Marshall Barton
Guest
Posts: n/a
 
      18th Jul 2008
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]
 
Reply With Quote
 
 
 
 
Bob Dancer
Guest
Posts: n/a
 
      18th Jul 2008
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]
>

 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      18th Jul 2008
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]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Form to show all records from one table, even if no match in anoth Jeff Borden Microsoft Access Forms 3 12th Nov 2008 12:12 AM
New Query, All Records in one Query, which does not exist in anoth Mercadogs Microsoft Access 5 3rd Dec 2007 10:29 PM
how do i copy a sound file from one powerpoint slide show to anoth =?Utf-8?B?amI=?= Microsoft Powerpoint 1 30th Aug 2006 08:00 AM
how to make one column copy from one sheet to anoth column w/o zer =?Utf-8?B?YXJlZXpt?= Microsoft Excel Misc 3 6th Jun 2006 10:45 PM
how do i get excel to see info in one cell, look at info in anoth. =?Utf-8?B?ZGl0dG8=?= Microsoft Excel Misc 3 1st Feb 2005 05:37 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:44 PM.