Results 1 to 8 of 8
  1. #1
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212

    How to duplicate the contents of 2 related tables.

    I have a company table A and a company personnel table A with a link field. After new companies or new personnel have been entered into this table, I would like to have the same content be in company table B and company personnel table B. The fields in the tables have common names except company A starts with L, and company B starts with S. Having slightly different names stops me from just copying both A tables into B tables after updating A



    As I am doing a lot of processing downstream I would like a solution that allows me to duplicate what I have in both table A's into both table B's. Any or all ideas would be welcome. Thank-you

  2. #2
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    I'm not sure I understand the logic of your requirement. Why do you want to duplicate the table? You can do it using a Data Macro in the after update event of the company A table.

  3. #3
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    double post, ignore

  4. #4
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    I want to maintain company A as different to Company B, thus Company A field names begin with L and Company B fields names begin with S. Because Company A and Company B are the same people but wear Company A hat sometimes and Company B hat at other times. From a user's point of view, the companies and personnel only have to be entered once, but used in 2 different ways.

  5. #5
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    OK, but from both the user's and your own point of view, why do there have to be two different tables if they're gonna have the exact same records? Doesn't this violate normalization? Like I said nothing stops you from adding a Data macro in the After Insert (or Update) events of your tables and use the Createrecord action to populate your other tables.

  6. #6
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks for all responses. I'm not familiar with Createrecord and couldn;t grasp the concept. But I have finally worked my way through to a solution. When exiting from updating records for Table A. I delete everything in the two Table B's, and append everything from the two Table A's into the two Table B's, thus creating a duplicate of the data right down to id numbers, which I wasn;t sure would act the way I wanted them to. I have been trying to solve this problem for several months. So now it seems really simple, but often it takes time to find a simple solution. So I'm happy. Anyone see any gotcha's in this approach. Thanks.

  7. #7
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    I'm glad you got it working, but I'm curious, is there a reason that you need two tables with the exact same contents? Do they ever differentiate? If not it seems kinda redundant...

  8. #8
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    I think because the same people act in a different capacity depending on the situation, there is a need for 2 different tables. Also it was designed originally assuming the contents would be different and I want to minimize the changes I am making. Therefore, to update the B tables with the A tables when changes are made, is to do it early in the process when it does not affect all the downside processing that takes place after that. So I think in this set of circumstances, this redundancy is acceptable. Thanks again for the interest, it really helps.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-06-2016, 06:05 PM
  2. Related Tables
    By roybb in forum Database Design
    Replies: 5
    Last Post: 08-27-2014, 03:30 PM
  3. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  4. Replies: 5
    Last Post: 12-14-2012, 04:21 PM
  5. Replies: 5
    Last Post: 04-18-2012, 12:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums