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