Add Rows from One Table to Another

[BigNate]

Hello Everyone,

I have one common field between the Table1 and Table2 called PartNo. Table 1 has many blank rows in it, which I want to keep as placeholders. Thus I cannot do a select query.

There are no duplicate PartNo values in either table (except for the blanks in Table1). How would I add three fields from Table2 to Table1?

I originally wanted to do a Vlookup in Excel, but I have too many rows of data.

Thanks in advance for your help!

 

[xenou]

You shouldn't need to add any fields, just do a regular query with joins.

Select Table1.*, Table2.Field1, Table2.Field2, Table2.Field3
from Table1
inner join Table2
on Table1.PartNo = Table2.PartNo

 

[BigNate]

You shouldn't need to add any fields, just do a regular query with joins.

Thank you for your reply. When I did this, the blank rows (placeholders) dropped from the results. Is there a way I can keep the blank rows?

 

[Joe4]

Change your "Inner Join" to "Left Join".

 

[BigNate]

Thank you for your help. This has brought me closer to the end goal, but not quite there. The result of the left join seems to be duplicating rows where the same value exists in Table1 and Table2. How could I change this so that rows are not being duplicated?

Thanks again!

 

[Joe4]

Please provide some samples of what the data in each tables look like, and what you want your expected results to look like.

 

[BigNate]

You know what, I just figured it out. I had duplicate values in Table2, which was causing duplicates in the output. Thanks again for your help!

 

[Joe4]

You know what, I just figured it out. I had duplicate values in Table2

I figured it had to be something like that. A Left Join will not create duplicate entries unless you have a one-to-many or many-to-many relationship.

You had originally said:

There are no duplicate PartNo values in either table (except for the blanks in Table1).

Note that you can change the Index on the field in Table2 to ensure that duplicates are not allowed.