compare two tables and populate a third one accordingly

[rohini]

Dear all,



I have a basic table that has a list of all codes possible - 'basic'

I have two tables, each with some information with respect to a code. It is not necessary for each code to be present here. in the given example, these two tables are 'input1' and 'input2'

i would like to integrate the data from these two tables into one using basic as a guideline. this should give me the table 'output'.

could you please tell me how this can be done?

Thanks

[alansidman]

Assuming the code is the common field, you can do this two different ways. You can either join all three tables on the code in a select query. Once you have done this, change the query to a make table query. Or you can add the two new fields to the table basic in design view. Then run an update query to take the data from the two input tables to the basic table.

Alan

[alansidman]

Assuming the code is the common field, you can do this two different ways. You can either join all three tables on the code in a select query. Once you have done this, change the query to a make table query. Or you can add the two new fields to the table basic in design view. Then run an update query to take the data from the two input tables to the basic table.

Alan

[rohini]

hey i did not really get you. code can be the common field but some of the codes are not present in input1 and some others are not present in input2 while some aren't present in either. so how do i make it a common field, i will only get those entries here which are present in input1 and input2. while i want all those that are present in input1 OR input2 or those in neither also to remain ....

if possible please make this query on the file i have attached so that i can see what you are saying..

[alansidman]

Look at Query1. After looking at it, change it to a make table query. Run it by selecting the exclamation point icon.

Here is the SQL statement

SELECT basic.Code, input1.location, input2.sale INTO basic
FROM (basic LEFT JOIN input1 ON basic.Code = input1.code) LEFT JOIN input2 ON basic.Code = input2.code;

You ensure that you capture all codes from the first table by left joining it to the other two codes.

[rohini]

thanks a lot!