Table Row Mirror Rows of Another Table

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
620
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,
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Dec 29, 2006
Messages
620
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Dec 29, 2006
Messages
620
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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)
 

Forum statistics

Threads
1,082,257
Messages
5,364,074
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top