Import with/without Foreign Key

[CDICKENS]

I am currently in the design phase of this database project (which is my first database by the way!) and I have a few questions. 2 of the tables I will have is inventory and orders. I realize that I need a foreign key in the orders table linking to the primary key in the inventory table. Here is the question. Does the Foreign key in the order record have to be in the import, or is there a way to query and lookup the Inventory primary key and insert it as a foreign key in the orders table after the import?

My brain seems dead at this point because everything I look at gives me the impression that I will need to create the Primary and Foreign Keys for all the tables in my excel sheets before importing, which while I know can be done, I still feel like I am doing most of the work in excel when I simply want to import my data with excel. Any suggestions?

Chuck

 

[Parra]

In a one to many relationship. You can not have a foreign key without it being the primary key field.

Its a big pain, but it does help out when running reports and matching revenue to the penny/yen/euro what ever it is your matching.

 

[SydneyGeek]

Hi Chuck, database design is one of those things that doesn't come easily (at first). There are plenty of rules for creating tables so that the data is stored in the right place and then can be retrieved. That usually involves a lot of work with pencil and paper before launching Access (or any other database application). The best rule that I have seen is
1. Turn off your computer...
Think of the table structure as the skeleton of the database. Put the arm in the wrong place and you'll have trouble designing a good body.
A pretty good free resource can be had from the Microsoft Knowledge Base. Go to http://support.microsoft.com and look for the link to the Knowledge Base. Once there, select Access as the application and "understanding relational design" (without the quotes) as the search string. You'll be directed to a zip file that you can download. It's a discussion of how the table structure for the Northwind database (installed when you install Office) was derived.
If you want to spend some money, look out for Database Design for Mere Mortals (Hernandez), ISBN 0-201-69471-9
Take it slowly first time and you won't have to start again in 6 weeks' time when things fail to work as expected. I've been there

HTH

Denis