very confused about relationships

[devxweb]

Hey, ok so in our exams, we are given a text file with many columns, so basically you have to import the file and split the one table into many, is there a way to split them other then analyzing? second thing one to many relationships are created when two fields are similar in two tables, how the HELL do you copy that field in into the other and create a 1:M relation... very perplexed!!! help me ASAP guys... if you can PLEASE show me step by step of how to do the relationship as soon as after importing the data... thanks!!

Edit: As you can see in this picture below, the tables are wonderfully related by 1:M and the tables were split from one and each table has foreign key higlighted which i can not understand how to create...help

[jzwp11]

Since this is for an exam, I'll just give you some pointers on how I would approach it. First, I would do the analysis and then from that setup the appropriately normalized table structure similar to what you show in your post. There are a couple potential issues with your table structure I see. It relates to the stock table. As you currently have it set up, it implies that a product can only be provided by 1 supplier. Is that correct or can many suppliers provide the same product? If many suppliers can provide the same product, a different structure is necessary. The other issue I see is with the field: [Number in Stock]. This should be a calculated value and thus not stored in a table. You would calculate this value on the fly when needed by adding up all of the quanities purchased less the quantities ordered.

As a general tip, it is best not to have spaces or special characters (*, -, (,), #, $ etc.) in your table or field names. The underscore is OK

Are the primary key fields you show in your post autonumber fields? If so, those would populate automatically as you push data into the tables.

I would import the raw data into another table that will eventually be deleted.

The rest depends on queries to copy the data from the import table to their respective normalized tables. You would start with the two outer most tables (suppliers and customers). You would create a select query that uses the import table to select only the unique supplier records (you will need the DISTINCT keyword in your query). Change the query type from SELECT to APPEND and follow the prompts Access gives you. Run the append query. You would use the same approach to populate the customer table.

The next table to populate will be the stock table. Again you would create a query. That query will need your import data table and the supplier table you just populated. You will need to join the two tables via a field (or fields) that exist(s) in both tables. Assuming that each product is supplied by only 1 supplier you would join the two via the appropriate supplier field(s). If you are using an autonumber field in the supplier table, you will have to rely on some other field(s) to make the join(s). If the import table is the one that supplies the unique supplierID then you would obviously use just that field. Select the fields pertinent to the stock table and then run the query to verify the info. Switch the query type to Append and run it. The stock table will now be populated.

The final table to populate will be the order table. Again create a query that has your import table, the customer table and the stock table. Again join the tables via the appropriate fields, switch the SELECT query to an append query & run it.

[devxweb]

Thanks alot mate. I tried the append query method but then I saw Make Table query type and that's the thing I wanted. Frankly Analysing tables in Access really sucks for me so am gona split the tables the query way. You have already saved so much time in my exam. Forget to mention that don't worry about the image, its just a sample i grabbed from a worked paper solution. Thanks again.

[jzwp11]

You're welcome. Glad we were able to help out.