Duplication of records in several tables 1

[dtutton]

From one form, Im wanting to set a command button to duplicate specific records in multiple tables. i.e. I have about 9 tables with up to 150-200 fields but all with the same primary key.

The data is for specific properties. So for example, I want to establish a duplicate set of records for 'Property X' as 'Property Y'. The way I do it manually is go to each table, select the record, copy and paste append and then edit the field name. Clumsy eh.

I can see how to do it for the table refered to in the current form but want to do it for all other tables.

Any ideas.

David

 

[kjonnnnn]

What about a lookup in the other records

 

[dtutton]

I guess thats part way there but after finding the record in each table, I want to copy it and then paste append it and then change a field name before updating.

I dont have a problem finding the record but as each record has about 200 fields, I dont want to have to copy each field to a default value before adding record. i.e. can one copy the whole record to an array and the add the array ?

Thanks,

David

 

[JerryDennison]

You have 9 tables that share a one-to-one relationship with 150-200 fields? This is the equivalent of one record with as many as 1800 fields!!! Sounds like you should seriously consider normalizing your database. How many of your fields share the same name but different number, i.e. field1, field2, field3, fieldn...?

 

[dtutton]

I split this way as certain tables are specific to specific users and come from separate sources via excel. Only common field to each table is the "project"

 

[JerryDennison]

You know your setup best. But it really doesn't matter where the source data comes from as far as normalizing goes. It's all a matter of structure. Of defining entities and their attributes and organizing these into a coherent structure. Without getting into the arcane aspects of the Forms of Data Normalization, I can tell you I have never seen any situation(granted I've only been working with databases for 20+ years so I can't have seen everything) where a discrete entity would have upwards of 1800 attributes. You really should re-examine your structure. Out of curiosity, I'd be happy to look at what you have and offer what I could(gratis of course).