Append Data to a table from another table.

Robulous

New Member
Joined
Mar 10, 2006
Messages
43
Hello...

I have two tables that looks (simplified) like this...

Table 1:

UniqueID - Name - City - State - Zip - Color
1234567 - Dell - small - CA - 80010 - BLUE
7654321 - MAC - Large - OR - 85214 - RED

Table 2:

UniqueID - Name - Occup - Title - State - Color
1234567 - Dell - Geniology - Analyst - CA - ??????
7654321 - MAC - healthcare - Athlete - OR - ??????

PROBLEM :devilish:

I need to get the COLORS to table 2... (using the unique ID's as a reference between both tables).

Any help would be greatly appreciated.

THANKS
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
The first question you need to ask, is do you really need to do this? Usually, if it can be done using a query, there is no reason to update the tables. As matter as fact, doing so could compromise the integrity of your database (what if after you update Table 2, a value in Table 1 changes - now the tables have discrepancies).

Usually, you only need to update the other table (instead of using a query) if you are trying to capture & store history (and not current values).

If you do need to update the table, take a look in Access' built-in help for Action Queries, specifically the "Update Query". This should give you details and examples on how to do it.
 
Upvote 0
?????? Now I don't know what to do...

I just need table two to capture table ones color field... you could use the unique ID to pull those over.

I don't know what to use now. I tried an "append query" and now I have 200,000 extra rows that I don't have any idea where they came from.
 
Upvote 0
An "Append Query" adds new records. An "Update Query" updates fields in existing records. So you would want to use and "Update Query".

But I go back to my original point. Why do you need it in the table 2 (because you can represent the exact same information in a query without having to update anything)? You can use query for just about anything that you use tables for, and unlike tables, queries are dynamic.

You can link Table 1 and Table 2 in a query, and return all exisiting fields from Table 2 in your query along with the color from Table 1.
 
Upvote 0
I tried a query

A query would be ideal...

I created a query...
brought in both tables
matched the unique ID's
Selected all the columns in table 2
Selected Color from table one.
and RAN it

I got back much more rows than I had in table two. So I had something like 150k rows in table two and the query returned 210k... I don't get it.
 
Upvote 0
Sounds like you have a linking problem.

1. Make sure that your unique IDs are truly unique within each table (i.e. within each table, each ID only appears on one record). You mentioned you did an Append Query that added a bunch of rows. Did you clean these back out?

2. Make sure your two tables are linked by the unique ID and nothing else. If you do not link the two tables, your total number of rows will be the product of the number of rows in each table.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top