Importing from a staging table with autonums

[nathanstevenson]

Hi,

I have built a database with autonumbers, to test whether or not it is better to use them. I have all my data in one big table with 28 different columns and 280 lines (for testing purposes). I want to split it up and distribute it into 8 different tables.

How do I maintain relationships if the primary ID on the staging table is "lower-down" on the list of one-to-many tables. I.E. it is on the many side.

For example, One of the tables is Manager, which has fields Division, Department,Cost Centre and Manager Name.

Manager has a one-to-many relationship with table Contractor. Contractor has fields, Contractor Name and Agency. Contractor has a one-to-many relationship with Timesheet, which has fields Hours Worked, Rate, Vat and Total.

It looks like this:

Manager -------------->Contractor----------->Timesheet
one 2 many one 2 many

The problem arises in that the imported data is comprised of all details recorded on timesheets which only leaves me with a possible primary key for timesheet. How do I append to Manager and contractor and finally to timesheet while maintaining the relationships?

Is there something simple I am missing here? Or is the only solution to write some code that will check for unique values over x fields, spit that out into it's destination table, insert a column in the imported data table, then update the imported data table with the primary key ID from the destination table where all x fields are equal to all x fields in import table?

If so, how would you do this?

Any help would be highly appreciated!
Thanks,
Nathan

 

[beetee]

I think your approach seems sound.

1) make a query with all the fields destined for the manager table, use group by, and append the records to the manager table, which includes an autonumber manager ID
2) make a query with all the fields destined for the contractor table. Again, use 'Group By'. Join that query with the manager table by it's primary key (e.g. manager name or employee id), and set a numeric ManagerID field in the Contractor Table with the autonumber ID in the manager table.
3) repeat the process for the Timesheet table, joining in the Manager table and Contractor tables linked by primary keys, and inserting Manager and Contracter Autonumber ID's.

You could do this all in one pass with VB code, using seeks to determine if a record has already been added.

 

[nathanstevenson]

Hi Beetee,

Okay, I got step 1 no problem. Appended unique manager records from table "Import" to table "Manager" which contains an autonum field for Manager ID.

Step 2 is the part I am having conceptual difficulties with.
Do I make an append query, with tables Import and Manager in the query window, and then in the fields section have all fields destined for contractor come from the Import table and then only manager ID(foreign key) from the Manager table. If so, how can they possibly be linked? It seems an arbitrary link to me.

Alternatively, if I create a select query from the Import table (with all the contractor fields), and then link that to the Manager table, how will the manager table know when a Contractor "belongs" to it?

I am just having difficulties with figuring out how the link is made.. if I understand it, it will probably fall into place.

Thanks,
Nathan

 

[beetee]

I think you have just about everything in step 2 correct; just one missing element. The key (no pun intended) to making step 2 work is to link the ImportTable and the Manager table by the primary key of the manager table.

Here's the concept I try to use for tables:
1) the primary key should be based on values in the table, e.g. company id, or height, width, depth, and weight, or whatever.
2) If the table is used by other tables, an autonumber index should be added; this index will be used to link with other tables.

In this case, the primary key of the manager table should be some set of attributes (originally from your import table) that uniquely identify the manager.

Then, when you make the contractor table, you can join the import table and the manager table by that set of attributes, and put the autonumber value from the manager table in the contractor table as the link to the manager table.

You can then use the same principle to link the Contractor table (which should also have a primary key composed of unique attributes, and an autonumber key) to the timesheet table. If you need to get from timesheet to manager, you would link using the ID's generated by autonumbers.

HTH