Unanswered: appending into one-to-many table structure using auto-increment Primary Keys
Hi,I hope someone can help.
I've got a project coming up where I'll be working with scientific data that has been collected
over many years by many people. The dbase and app. was originally created in Access, rolled up to Access 97,
2000 and then 2002. There is a master copy and an number of 'distributed' copies where each will have new data
that needs to be added to the master. Any one of them could be in Access 97, 2000 or 2002, each with different
versions of the data-model and application.
Additionally, the schema uses a series of complex primary keys. At the most detailed level, there are up to 9 fields creating
the primary key (PKY) for a single table. The system works, sort of but not without its challenges.
My question is this. To make it easier to work with the data, I want to redesign the data model and encorporate the use of
auto-increment fields for the primary keys. When I populate the new model with data, I'm not sure of an efficient
method of cascading the primary/foreign key relationships down to the one-to-many relationships.
I plan to automate the proecedure w/VBA as I can manipulate/correct the data while populating. What I'm not sure about is getting
a new record's primary key value passed from the parent table down to the child table in the foreign key field. I've got some
ideas and have done this before but I believe my method, is clumsy.
lets say the parent table has 112 records and the current PKY value = 112
A newly inserted record into tblParent.PKY_AutoIncrementValue = 113 and is the FKY value to join to
the child table on the FKY. The new tblChild.PKY_AutoIncrementValue = 2027 and is then used to join tblChild_Child.FKY_Value
And so on and so on...
The way I've tried this before was to insert the new record into the parent table;
Then, query for the max PKY value (113) and pass it using a variable to be inserted into the child table as the FKY value. Then query for the new Child Max PKY value in the child table (2027)
to be inserted into the Child_Child table.
It worked but there's a lot of code involved for only a few tables. This dbase has over 40 tables and there's got to be a better way to do it. I haven't found any examples on this.
I'm more of a data modeler than developer and would very much like to hear your thoughts on this.
There is a lot of convolution there. I think your asking for advice on how to bring all the disparate data together as well as possibly release a new version of the app.
In which case, I would lose all the matching of auto increment numbers and that sort of thing. It is imperative that you figure out what the real uniquely identifiable info is and use that to correlate the data.
There's a number of issues with this dbase that just makes it difficult to work
with unique values other than an autoNumber pimary key structure:
The database hasn't been normalized properly. I need to take it to the third level.
Currently, some of the detailed tables (child_child_child.....and so on) have over 100,000 records. Once normalized, some tables will have over 200,000 records.
The current primary key constraints have been 'unset' and they are not all unique due to null values etc. Part of my mandate is to also 'clean' the data.
Currently, queries aren't working properly due to all of this and other issues.
Using the autoNumber will be a huge help when adding data from the satellite data sets to be put into the new 'master'.
Knowing the approach or method to program or configure the act of carrying an updated parent autoNumber primary key down to the related child PKY for bulk data would be a tremendous help.