Foreign keys???? 1

[ljjtek]

OK,
Well, I have gotten a lot of help from people and I can't tell you how much I appreciate it. But now I am stuck. I am trying to follow advice that I was given but it involves foreign keys and I am lost....The access help files, aren't helping me! I don't even know how to add one!! If it is any help to anyone trying to help me, what I am trying to do is:

thread181-67987
Sorry, I don't know how to link so I guess it'll be a cut-n-paste job! But basically, I just need any help anyone can give me.

And yes, I am a newbie!!

 

[gwinn7]

A foreign key in one table is simply the primary key of another table. Generally, at least I don't, literally set a "Foreign Key" status to a field in a table. When it generally comes into play is when you make a JOIN between those keys in a query.

To make a "link" or a JOIN, try making a new query in Design Mode. Then, add the two tables that are related by the Primary Key and the Foreign Key (Relax! its just a copy of the primary key from the other table!). To set the link or JOIN, click on the primary key and drag that field to the other table's foreign key. A line should be drawn between them. When you check the SQL of that query you just made, it should say something like...

"SELECT * FROM TableA INNER JOIN TableB ON TableA.ID = TableB.TableAID;"

There are several types of JOINs (check the Help for more information).

Does this help ?

Gary
gwinn7

 

[Rohdem]

Let me throw me two cents in and see if it helps....
You do NOT need to tell Access anywhere that you want a field to be a primary key. You just need to set up a relationship between the two tables. Take the example of an order entry database. You have two tables:

Order Header Table:
OrderID - autonumber - primary key
Name
Address
City
State
Zip

Order Detail Table
OrderID - not a primary key, but a foreign key
ItemNumber
Description
Quantity

The field that links the two tables is OrderID.

Now in Access you can view table relationships be clicking the relationships button that is on the toolbar near the right hand side (hover your mouse over the buttons until you find the one that says relationships).

Now click the button that has a plus (+) sign next to a table. Double-click the tables that you want to show. Now click and drag a line between the two fields that you want to link. Next, double click the line and select "enforce referential integrity".

You have now created a one-to-many relationship. This means that you can't have a record in the Order detail table without having a related record in the Order Header table. Remember, you do not have to define a foreign key specifically when you are creating a table.

Hopefully, this will help a little...... Mike Rohde
rohdem@marshallengines.com

 

[ghubbell]

And my two cents worth...but you loose quite a bit in the exchange...

Hi Lauren, you're doing better than I...at least you got the Thread link to work!!

Lets work with copies of everything so first rename your tables "YourTableNameX", then recopy it as the real name again. Take your first table. Clients I believe. In design view go to the indexes button (just right of the key) click it and delete all the indexes you see.

Let's add one field to it. Name it ClientID, and make it an autonumber.

Select this field and click the key button on the tool bar.
This means it is now the primary key. No duplicates allowed. Now being as it is an autonumber it will of course always be unique and won't stop you from entering Client "Gord" twice, but it's a pretty good start. Switch the table to datasheet view, saving on the way.

Your ClientID is now unique to each record. period.

Your next table which I guess is your "ClientContacts" needs to be built or arranged and have the "foreign key" installed:

You answer yourself in your first post "one client has many contacts": You achieve this with the second table and its fields might look like this:

ClientID <- a long integer allow duplicates yes! but Required at this point No.
ContactID <- a unique number (autonumber) for each contact (record) in the table.
FirstName <- text field
LastName <- ditto
More Fields like their phone, extension, fax....

Remember: One Client----Many Contacts
1 ClientID in clients table------ Many ClientID in contacts.

You already have data in the tables so if you have 10,000 records we should build some queries to do the work for you, but if you only have a dozen or so records you can do it manually. Open both tables on the screen at the same time. Choose window on the tool bar and tile horizontally or vertically...however you prefer. As long as you can see your two tables clients names at the same time. In the Contacts table you will have to type in the appropriate number that matches the name in the Clients table. Client TTdotCom is #1 in the clients table, Contact Gord in the contacts table gets #1 in the contact table ClientID. Lauren is also a contact at TTdotCom so she gets a #1 beside too.
When you have this completely filled in, you should flip the contact's table in to desigh view. ClientID &quot;Required&quot; should be switched to Yes. Save and say yes to a bit of nagging MS might do.

To the relationships window! (Right click when you are on the &quot;database&quot; window...the first one you see when Access starts.) If you dont see your two tables already in the view you'll have to add them. Click the funny yellow + button and add the two. You should see the ClientID (darkened) in the Clients table. You should also see the clientID in the other table but it won't be dark. Click on the ClientID in the client table and while holding your left mouse key down draggggg it over to the Contacts table and drop it (release the mouse button) while you're over the &quot;Foreign&quot; ClientID.
An &quot;Edit Relationship dialog box should pop up (you can activate this anytime by double-clicking on the relationship &quot;Line&quot; you just made...). You choose the &quot;Join type&quot;: Usually here you want to enforce referencial integrity, and at least Cascade update. Cascade Delete is your choice meaning if you delete TTdotCom as a Client you will automatically delete Lauren and Gord.

If everything works to this point you're now safe to go back to your Contacts table (if you haven't already) and delete the field that used to store the Clients name. You don't need it any more. Your client's name can at anytime be obtained by refering to the ClientID that &quot;starts&quot; in the Clients table.

See how you fair with all this and let everyone know how it goes. We'll get it! Gord
ghubbell@total.net

 

[scking]

Here's my comment. You don't HAVE to set a primary key but if you don't you may not have a unique key and could get duplicate records in the database. You also wouldn't get an index created for you automatically which would seriously degrade performance if there is a lot of data. NO you don't have to do the design work necessary to build good relational database but I would highly recommend it.

One of the things that Microsoft doesn't tell you when you buy Access is that you will need to model and design a relational database. To compound that oversight they also don't provide you with any information in the help files about designing databases. I've seen comments in their documentation to the effect that you should but it isn't covered there either.

Steve King
Professional growth follows a healthy professional curiosity

 

[ljjtek]

Just wanted to say thank you guys for your help! I have made it work and couldn't be more greatful for all your help. I have plenty more questions......I'm sure you'll see some pretty soon!!

Thanks, again
ljjtek