Table Row Mirror Rows of Another Table

[bs0d]

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.

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,

 

[xenou]

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!

 

[bs0d]

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).

 

[xenou]

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.

 

[bs0d]

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

 

[xenou]

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)