If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Query to show all records of one table and updated info from anoth
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] |
#3
|
|||
|
|||
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] |
#4
|
|||
|
|||
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] |
Thread Tools | |
Display Modes | |
|
|