Access Questions. (1 Viewer)

[SoCal]

If anyone can help me out here, it would be greatly appreciated. If I have an intersection relation (i.e. it has two foreign keys) how do I make Microsoft Access allow two primary keys? It only allows me to have one primary key. Also, when I am entering new records, it won't let me to enter two of the same bits of data in the primary key field. I need to have the same person doing more than one thing, and it is not allowing me to do so. Can someone please help me out here? Thanks.

 

[sunny]

how do I make Microsoft Access allow two primary keys

To make a composite key:
Highlight both fields in the design view, and while holding down shift right click and select Primary Key to make them both primary.

Also, when I am entering new records, it won't let me to enter two of the same bits of data in the primary key field

Thats the nature of a primary key. Primary keys are used to uniquely identify each record in a table. Hence, if two primary keys have the same value the primary key no longer serves its purpose.

 

[SoCal]

Originally posted by sunny
To make a composite key:
Highlight both fields in the design view, and while holding down shift right click and select Primary Key to make them both primary.

Thanks for your fast reply sunny. When I tried that, I get an error message saying, "You already have an index named 'PrimaryKey'" and won't allow me to do it?

Originally posted by sunny
Thats the nature of a primary key. Primary keys are used to uniquely identify each record in a table. Hence, if two primary keys have the same value the primary key no longer serves its purpose.

That is what I thought, but I have to do it this way, and it shows that I can do it this way in my text book. What other way could I do it?

 

[sunny]

Originally posted by Merethrond
Thanks for your fast reply sunny. When I tried that, I get an error message saying, "You already have an index named 'PrimaryKey'" and won't allow me to do it?

Are you actually trying to call the field "PrimaryKey"?? If you want two primary keys, this is what you should get.

 

[SoCal]

Originally posted by sunny
Are you actually trying to call the field "PrimaryKey"?? If you want two primary keys, this is what you should get.

No, I am not trying to call the field "PrimaryKey". I can get the screen to have the two primary keys (like in your attachment) but when I go to save it, I get the error message.

 

[sunny]

Do you mind if I look at your file?

 

[SoCal]

About having the same values in the PrimaryKey. I have attached the example in the text book. As you can hopefully see there are several times where the same Employee ID is used in the PrimaryKey, in order to show that some employees have multiple jobs.

 

[SoCal]

Originally posted by sunny
Do you mind if I look at your file?

That would be no problem, except that the size limit for an attachment is 100KB and my Access file is over 400KB.

 

[sunny]

In that case, I think you have misunderstood the diagram.

What you see in the diagram are not actually seperate tables.

Especially the Job Assignment Relation - it is meant to be a composite entity between Job and Employee to split its many to many relation into two 1:N relations.

Since the primary key in the Job Assignment Relation are Employee ID and Job Number (and assuming an Employee can be assigned to a Job twice), the composite key should work with no problems.

 

[SoCal]

Originally posted by sunny
In that case, I think you have misunderstood the diagram.

What you see in the diagram are not actually seperate tables.

Especially the Job Assignment Relation - it is meant to be a composite entity between Job and Employee to split its many to many relation into two 1:N relations.

Since the primary key in the Job Assignment Relation are Employee ID and Job Number (and assuming an Employee can be assigned to a Job twice), the composite key should work with no problems.

Aren't composite entity and intersection relation the same thing? You are right, I am trying to break my M:N ER Diagram into two 1:M relationships. So, are you saying that because it is an intersection relation, it doesn't need to have PrimaryKeys assigned to it?

 

[sunny]

It still does require a primary key. But if you have managed it as a composite key, you shouldn't be having the problem you are describing. You could email me the file if it doesn't fit.

sunny at boredofstudies.org

 

[SoCal]

OK, sent.

 

[SoCal]

Oh, by the way, in the "Classes Table", the forms "CourseTeaching" and "NumberOfCoursesTeching" shouldn't be there. I forgot to delete them before I sent it to you.

 

[SoCal]

So did you get my email?

 

[SoCal]

Oh, I got it working! Well not really. All I did was create another table and it is working fine for me now!

 

[RUB!X]

lols