How do I split 1 table's data into many existing tables?

[nathanstevenson]

Greetings,

I am trying to import a large excel file into multiple tables in Access. If have managed to assign unique ids to sets of data within the Excel file so that the fields in the Excel file directly reflect those in the tables. I have also managed to import that file into a staging table in Access. My problem is now:

- How do I maintain the relationships between each set of data?
- Is it a case of multiple appends?
- If so, how do I make sure that duplicates are NOT added given that the primary key (for each table)is already uniquely defined in this temporary table (and there are no autonums in the tables)
E.G. There are 150 contractors, 200 managers, 30 Assignments etc but 780 entries.
-How do I automate this so that 1 temp table can be split into 9-10 other tables?

Thanks in advance,
Nathan

 

[AccessAce]

So are you just trying to convert a flat file into a relational database?

 

[nathanstevenson]

It's more complicated than that but yes.

These are the complications:

- the same data has to be imported regularly so a make table query cannot be used (i.e. to maintain relationships)

- I have generated unique IDs as need be in the flat file, effectively creating a relationship thread for each entry.

How do you append entries to a table, when there are duplicates, but you want the duplicates to be skipped?

Also say all these 9 tables have relationships defined in them, so all the data is related. How do I put the data from this temp table into those 9 tables (given that I have managed to create Primary keys for each table). I am struggling with the idea of how to put all the data together in one go so that the relationships will be maintained.

 

[AccessAce]

Here's the thought.
If the unique identifier for each of the subtables is defined in each record correctly, then when you try to append that record, it will catch the duplicate ID and will ignore the append request, thus rejecting any duplicate.
If the data is split into 9 tables, a tenth table will have to be created with nothing more than the primary keys from the 9 tables. This tenth table will be the basis of your query to recreate the original content.

Does this makes sense?

 

[nathanstevenson]

Yeah, that's great exactly my track...

The thing is, how do I get the data into those 9 tables while maintaining referential integrity?
E.G. I can start with table 1, which is related to t2, and so on till t9 and start appending from t1 cascading down to t9, but an Append query will only allow me to append to one table.

If I had to do it manually, I'd enter data in t1, which would require me to enter data in t2, and so on to t9, but importing it in just makes it so difficult.

Any ideas?

 

[AccessAce]

Start with the table that has the least number of resrictions and work your way down the chain. Meaning:

If no entry can be made in Table1 unless there is a related record in table2, insert into 2 first. etc.

Another thought would be to drop all relationships before the import. Do the import. Then restore the relationshps. (I don't recomend this approach.)

 

[nathanstevenson]

Hi AccessAce,

Some feedback first - your advice worked brilliantly!

Just coming back to this from a different perspective now, as I am testing this in an autonum DB, to try and make it easier to import on a regular basis.

You said with 9 tables, the 10th one with all the keys would be the basis for reconstructing the orginial data.

Question: If you only have 1 key and it is not no.1 in the chain of keys, how do you split up the data AND maintain the relationship between the 8 "sets" of data within the staging table?

 

[AccessAce]

If there is a piece of data that only follows a portion of the relationship tree, you should be able to leave the remaining keys null as now record would be found in the tables those fields reference anyway.
If data is partial at the lower levels, dummy entries in the upper levels will have to be enetered to maintain ref. integ. anyway.
I'm sorry if this sounds confusing, it's been a while since I last looked at this thread and I may be a little out of it. I'd be happy to help where I can though.