Results 1 to 6 of 6
  1. #1
    davefitz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    3

    Copying data from a series of tables in a one-many relationship into the same tables

    Hello all,
    This is my first post so please forgive me if anything here is inappropriate. Thanks.


    I have an Access problem that I was hoping someone could give me some direction on.
    I am trying to duplicate a series of related records within the same database.
    The database stores "protocols" for medical conditions and I would like to duplicate entire protocols to then change parts of them, to compare to the original protocol.
    So, the database has a series of tables e.g. Disease table with 1:Many link to Severity table with 1:Many link to treatment table.
    (this is an example).

    If I add my first lot of data, the fields look like this, where a single entry into Table1 has 3 matching entries in Table 2 and each entry in Table 2 has four matching entries (linked data) in Table 3.
    Table1 Table 2 Table 3 
    ID Disease ID2 ID Severity ID3 ID2 Treatment
    1 Cancer 1 1 Mild 1 1 A
    2 1 Moderate 2 1 B
    3 1 Severe 3 1 C
    4 1 D
    5 2 A
    6 2 B
    7 2 C
    8 2 D
    9 3 A
     10 3 B
    11 3 C
    12 3 D

    Now, I want to duplicate all of the above so that there are essentially 2 records in table 1, 6 records in table 2 and 24 records in table 3. I can then change some of the data linked to the second ID in table 2 (a variation on the first protocol). Does this make sense. The protocols often only change slightly which is why it would save me time to automate this copy and paste.
    My data after duplication will then look something like this:
    Table 1 Table 2 Table 3
    ID Disease ID2 ID Severity ID3 ID2 Treatment
    1 Cancer 1 1 Mild 1 1 A
    2 New Cancer 2 1 Moderate 2 1 B
    3 1 Severe 3 1 C
    4 2 Mild 4 1 D
    5 2 Moderate 5 2 A
    6 2 Severe 6 2 B
    7 2 C
    8 2 D
    9 3 A
    10 3 B
    11 3 C
    12 3 D
    13 4 A
    14 4 B
    15 4 C
    16 4 D
    17 5 A
    18 5 B
    19 5 C
    20 5 D
    21 6 A
    22 6 B
    23 6 C
    24 6 D


    I will then be able to compare standard "Cancer" protocols with "New Cancer" protocols.
    My question is that I'm not sure how best to do this.
    I have a form where the user will select the protocol (with ID) that they would like to copy or use as the basis for a new protocol.
    I figured that I could have a listbox linked to where the user selects the protocol and then using VBA cycles through all the records updating each table as I go. I understand that to do this I will have to make use of foreignkey fields and also keep an eye on the original data's IDs to maintain links etc, but I'm not sure of the best way to do this.

    Any advice or direction would be greatly appreciated.
    Thanks

    David

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I think instead of duplicating data you should be using queries to display information from previous events.

    From what you explained here it seems you will need a little more than a simple DB. You are going to need several tables. Maybe you can start by studying some normalization techniques and realtions or Primary Key Foreign Key Relationships. You can't really build a DB thinking about what it will look like after the queries and forms are built. You need to build it on how best to group your data, so you can create fields and place these fields in their respective tables.

    Perhaps you can copy a contacts table from a template somewhere and rename it tblPatients or something. Then a nother table would only have a few fields to hold data about conditions. A third table could be used to log events.

    THis third table would have several fields. It would have several foreign keys in it too. Each time some activity happens, you can addd a record to the table and give it a Date/Time stamp. You would include info in its FK's to relate to the PatientID and Disease, etc.

    I know there are other things you need to include in your DB. But I would start with these three tables. With this, you can start to make queries and create relationships with your PK's and FK's. Now you can start to imagine what a form would look like. Create some queries and cause them to function in a way you can add records to log events....

  3. #3
    davefitz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    3
    Thanks so much for your reply. This is excellent advice.
    I have been searching the internet and I think what I am trying to do is "append data to multiple tables while maintaining relationships". The sample I included above was just an example. I have the tables all established with links (1 to many) and foreign keys included. Essentially I will use a query to generate a dataset which will include all the data associated with a particular protocol from the uppermost (primary) table. I then need that entire dataset copied so that it can be edited as a new protocol.
    I'm sorry if this doesn't make sense, but perhaps 'appending' is the best way to describe this, rather than copying.
    Does this make sense?
    Thanks in advance,
    David

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I think you explained it fine. You can create an append query very easily. Simply create a normal SELECT query using the query builder and save it. Now you can turn in into an action query by using the controls in the ribbon at the top of Access. There is an option to turn your SELECT query into an Append query. It will ask what table you want to append the data to etc.

    I believe you will quickly discover that choosing when to run the query, and what records to select, and where to append the selected records to, is cumbersome at best. There are instances where carrying information over from a previous record to a new record are important because fields from the previous event don't change for the current event.

    I am imagining your needs are not to carry over most fields but maybe only a couple fields. You can do this in a form and you can also display historical data within the same form. So my argument is don't duplicate data. You will have to decide for yourself, after using conventional methods, when it is best to bend the rules of convention.

  5. #5
    davefitz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    3
    Thanks so much - we do need to carry over all fields and your suggestion to use an append query is great. I am thinking that I can use a few of these and feed the foreign key into each, updated with the id value retrieved using the DMax function. I think this will work. I will give it a go. Your direction here has been terrific.
    I will let you know how I go.
    Thanks
    David

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Try out a few things and see where it leads. Post back here or start another thread if the question differs from the discussion here.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-29-2013, 01:20 PM
  2. Copying Linked Tables Programmatically
    By DarkWolff in forum Programming
    Replies: 1
    Last Post: 06-05-2012, 09:54 AM
  3. Replies: 3
    Last Post: 09-12-2011, 12:53 AM
  4. Copying Data in tables
    By Hannah in forum Forms
    Replies: 4
    Last Post: 06-22-2011, 06:16 AM
  5. Copying tables
    By Bajaz001 in forum Programming
    Replies: 21
    Last Post: 04-11-2011, 03:51 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 - Senior Forums