Import from a Text File while maintaining database relation

[robbeh]

Ok for the of simplicity I am going to use a dummy example:

Three tables :
Table 1 Items:
ItemID - PK
Item


Price

Table 2 Customers:
PersonID - PK
FirstName
LastName

Table 3 Orders:
OrderID - PK
PersonID - FK
ItemID - FK

My data is in a text file.
OrderID: 0001
Fname: Rob
Lname: Bebob
ItemID: 039
Price: 9.99

My question is, how do I get that into my tables? 1) Automatically 2) Manually. Just a little confused with the whole KEYS situation.

[orange]

A few questions:

1 Order only contains 1 Item (that is not very realistic)
1 Item has 1 Price (price never changes - also not too realistic)
Where do you get the PersonId from?

I would suggest you get your tables and relationships set up to match your business requirement. Then create some test data and scenarios, and work the data and scenarios against your data model. Make sure it ll works before building the database.
Once your testing and adjustments to the model meet your needs, then build the tables and relationships in Access.

Now the question is how to get the data you have into your tables.

One way often recommended is to build a "Temporary table to collect records being imported". If you always have the same number of incoming records (5 fields/value pairs in your example), you could set up a loop to populate areas in your Temporary table. Then create a number of queries to move the data from the temp table to the specific tables.

How many records are involved?

Good luck.

[robbeh]

Clearly my database design skills need some work. It's actual for the Poker database from the other section. I was just trying to simplify it for everyone and eneded up giving a bad example!

There are 30 000 records involved. I was confused because I was trying to learn the FK and PK stuff which I somewhat always knew about but now trying to implement it I am having a tough time learning it.

When I have all my relationships set up, it seems to work but it's weird looking at a table with a bunch of ID's and no info. But I see now when I go to the other tables there is the relationship data. Slowly I am coming around.

My thoughts for the 30000 records were using SQL and loops/conditionals. Where I am kind of stuck right now is entering information with Primary Keys, if I am looping through information and putting it into one table, and that table has a FK to another table's PK, how to I know what the PK is going to be for the FK (If I haven't entered that information yet for the other table)? Does that make sense?

Is that something I need to consider then? Making sure i enter the non-dependent data first then the data with the linked information?

[orange]

I guess the best place to begin is to get us back to the post you were working on and include the things you have learned or thought about since that post. Once readers understand WHAT you are trying to do, they will offer suggestions/options of how to do it.

Making a simple example often doesn't work -- as you can see.

Tell us in plain simple English what you are trying to do. Best is to show some before data and the expected output data.