Foreign Keys in Access 2007

[blueuk]

Hi,

I have to create a database for work and for my sins I have to use Access. We are running 2007 but I'm having some difficulty in creating foreign keys in my linking table.
I've searched their help guide and looked on the net but I can't seem to see how I physically create foreign keys.

My structure looks like this:

Customer
CustID (autonumber)
Cust Name
Cust contact
Cust email
cust version
no of users
other info

Modules
ModID (autonumber)
Module name
licensed module? (tick box)

MiscSoftware
MiscID (autonumber)
Software
Installed on live

CustMod
ID
CustID
ModID
MiscID

Thanks

 

[Dennisk]

you have a foreign key in your CustMod Table called CustID so I assume you created the table and columns. So is your problem something else. You may be referring to referntial Integrity in which case that is set up in the database window and not table design.

 

[blueuk]

Thanks for your reply,

Yes I did make these tables and I have referential integrity. However, it doesn't seem to be visually showing that it's a foreign key. I know that in 2003 I was able to add foreign keys.

Ultimately though the reason I want to do this is because I'm trying to make a form which contains the customer's details whereby they can select from a list the modules and software which they use. I only want the CustMod table to be populated though (to stop redundant data)

 

[boblarson]

doesn't seem to be visually showing that it's a foreign key. I know that in 2003 I was able to add foreign keys.

What are you talking about? I guess I don't understand.

As far as I know the way you "add a foreign key" is you create your tables like thus:

example:

Table 1 - tblCustomers
CustomerID - Autonumber (primary key)
FirstName - Text
LastName - Text
etc.

Table 2 - tblOrders
OrderID - Autonumber (primary key)
CustomerID - Long Integer (foreign key)
OrderDate - Date
etc.

And in the relationships window I drag customer ID from the tblCustomers to tblOrders and it comes up with the Referential Integrity dialog which lets me choose to enforce R.I. and select Cascading Updates / Deletes.

That has not changed in Access 2007 you can still do that.

 

[blueuk]

I've done everything that you said.

However, I'm certain that in Access 2003 you could specify with a button that a field was a foreign key.

Also, in the relationships screen it would be emboldened when there was a foreign key. Basically I'm having trouble getting the form for CustMod to populate the CustMod table as it doesn't seem to recognise the data from the linked tables.

 

[boblarson]

I've done everything that you said.

However, I'm certain that in Access 2003 you could specify with a button that a field was a foreign key.

I've used Access 2003 for 4 years and never saw something like that.

Also, in the relationships screen it would be emboldened when there was a foreign key.

It does if you have things set properly. The table that has the foreign key in it also needs it's own primary key or else it won't.

Basically I'm having trouble getting the form for CustMod to populate the CustMod table as it doesn't seem to recognise the data from the linked tables.

feel free to post a copy of your database (first compact and then zip it by using WinZip or something similar).

 

[blueuk]

I've attached my database in a zip file. I think it's in 2003 format though (as I had to take it home)

I've been trying to get everything set up correctly so that I can create one big form (to give to the customer) which allows the user to select modules and software from their respective tables which then populates the linking table.

Thanks for all your help

 

[boblarson]

1. The relationships seem to be fairly in order.
2. You should get rid of special characters in your field names and object names (no question marks ? in them)
3. I would remove embedded spaces in object names and field names to make life much easier (you can just change the labels to have spaces, but when you refer to items you don't have to enclose them in brackets most of the time unlike you have it now).
4. I was working on modifying things to show you a good way to do this but I have to leave shortly for some medical tests that will probably take most of the day. So, maybe you'll get additional help from someone. If not, I'll work on it some as I can later, if I get the chance.

 

[token_remedie]

does that mean that CustomerID will be the same in Table1 as Table2? I'm working with 50000 records, and the unique data I can split up into 15 tables, does that mean each table is going to have 50000 records? Or is there a way to create a table with the unique record (in some tables it's just one entry) and then insert the primary key from the main table after the table has been created?

 

[jdraw]

Bob's advice in post #8 is dead-on.
No need for spaces nor special characters in field or object names.

I have Acc2003 and have no idea of any button to specify Foreign Keys.
You can click a button to show/adjust indexes.

If you look at Tools -Relationships the diagram will show you your tables and relationships. The fields at the end of the relationship lines show the FK. The bolded field names in the tables show the PK.

You say you have 50000 records --- which records? 5000ustomers? 50000 Modules?
It isn't clear what you are asking. Can you please describe exactly what you want to do?

 

[token_remedie]

all good I worked it out thankyou