Table Row Mirror Rows of Another Table

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
So I have a corporate database that lists unique items and properties for the items in a table. This table is linked to a local access db, and I cannot add fields to it.

Therefore, I'd like to create a table that mirror's all the same unique rows of the corporate table -- and have my own properties (fields) associated with them. This way, when a new item is added, I don't need to update my local table, it will automatically update by itself.

I thought I could do this by making the property_id primary key field a lookup.

Code:
Select corporate.property_id from corporate;

Limit to list, no duplicates . But this results in a combo box populated with the id's, rather than populating the rows of the table with the ID's... This is probably an easy solution, but how can I achieve what I'm after here? Thanks,
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You can put the combobox on a main form and put the table rows in a subform. Then link the subform to the main form on Property_ID = Property_ID. This will effectively make the combobox into a navigation control.

I assume that's what you want - I don't see how it's going to add any rows to any tables but that's not what a combobox is for anyway!
 
Upvote 0
Yes, there's a misunderstanding of what I'm wanting.

Lets assume [table_A] contains 10 rows, where the primary key is field [property_id]

I want [table_B] to auto-magically contain the same rows (10), with the same unique identifiers [property_id].

Thus, whenever the company adds property_id's (rows) to [table_A] (which is read-only), my table [table_B] will reflect the new properties as well (which is read-write).
 
Upvote 0
You would want to run an insert query:

Insert Into Table_B SELECT * FROM Table_A;

When you run the query records with key violations will be discarded. I don't think you can do this automagically but you could have a macro run it when the db opens. Depends on the situation. That still might leave the tables out of synch if new data is added while you are working (this might be true anyway). So you may want to update it on demand when you are using the tables.
 
Upvote 0
Would the insert statement overwrite existing? If the property_id already existed, and you ran the insert query, would it:
• insert again
• overwrite existing
• skip
 
Upvote 0
Hi, this depends on the indexes. If there is a unique index on the property_id, it wouldn't allow an insert again. (note: primary keys are always unique indexes by definition). So that would be skip. If there is no unique index on the id then duplicates are allowed so that would be insert again. Overwriting doesn't occur in either case.

If data is changing and you need to update values for existing Ids, in addition to adding new rows for new ids, then you have to do a two-stepper:

1) insert (for new Ids)
2) update (for existing Ids)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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