Changing Primary Key

[lubbs65]

So I am trying to create a very basic tasks database right now. After get the basics down it should get immensely more complex but will implement everything done in the basics. Right now, I am trying to find out how to change the primary key of the default "Contacts" table. It is currently set as ID number and I am trying to change it to Last Name. As of right now there are no duplicate last names so I don't see why it is giving me problems.

When I try to change it I get the following error:
You can't change the primary key. this table is the primary table in one or more relationships. If you want to change or remove the primary key, first delete the relationship in the Relationships window.

I've been trying to locate the relationships window (obviously i've googled for help) but still can't find out how to remedy this error.

 

[boblarson]

Just an FYI - Using a last name (or ANY name for that matter) as a primary key is a very bad thing to do. Not only is the chance for duplication - real duplication that is necessary - a greater risk, but a numeric key can be faster when searching records. Now, if there are not a lot of records that might not be as noticeable, but it can be when used with lots of records.

Also, a non-surrogate primary key means that there is also greater risk of needing to change if business rules change. This is not to be taken lightly. This can cause major rewrites to your database. And it DOES happen and more frequently than you can imagine.

So, my suggestion is to stay with an autonumber for the primary key. The primary key is really only good for the SYSTEM anyway, to maintain the referential integrity of the data and the relations (relational database). So, the user should not care what it is.

That's my suggestion coming from 14 years of Access development experience. But your choice.

 

[lubbs65]

Thanks for the tip, I plan on making changes later. Right now I am just doing a light practice for errors that will occur. Later on i am redoing/adding plenty of fields and adding about 3 more tables to the mix.

 

[boblarson]

Do not do it!!!!!!

This worked however I feel like it will be problematic. Here is why:

I want to create a form that allows you to select one thing, then auto-populates the other values (this is now the second step that i'm trying to figure out and I may be posting questions later).

The thing to remember is in a relational database you do NOT store the same data more than ONCE. So, you should NOT be storing the last name and the first name in another table. You should be storing an ID which POINTS to that record from the table where they are defined.

So anyway, if I had two contacts: Dave Smith, and John Doe who had different phone numbers lets say.

Originally I want it to be: you select Smith via combo box. then Dave is auto filled in the form as well as his number. The same would apply for Doe.

This is ludicrous. What if you have Dave Smith and Jennie Smith and Ronald Smith? When you select Smith, whose name goes in the first name spot? So, you would need to have the Last Name combo include the first name, which is also doesn't make sense because then you are duplicating things all over the place which don't need to be duplicated.

Since I now have to keys would I have to fill out "Smith" for last name, then "Dave" to then have the number field get auto populated?

Get back to the original SINGLE autonumber KEY. It will save you a LOT of work and also I think you should read this about NORMALIZING your database. Because storing the two names as a key (composite) is not normalized and it is not wise.
http://support.microsoft.com/kb/283878

 

[lubbs65]

Re: Do not do it!!!!!!

Get back to the original SINGLE autonumber KEY. It will save you a LOT of work and also I think you should read this about NORMALIZING your database. Because storing the two names as a key (composite) is not normalized and it is not wise.
http://support.microsoft.com/kb/283878

I was under the impression that you need the key for the auto population though. Meaning:
Say i have Bob Jones, Kate Jones, and Dave Smith. They all have phone numbers. I need my form to select them by name. THIS IS A REQUIREMENT. Perhaps I will just create a full name field rather than a first and last to remove duplicate last names. Anyway, I need to select the name, then after the name is selected on the form, there phone number is automatically filled in properly as well into the form. That way when I submit the form later. I can see all that data in the base table (right now seeing there name and number is pretty useless but I end up doing much much more. once i get this fundamental concept down I can really accomplish what I need to).

Are you then implying that if I were to have a full name field rather than a first and last, that any field can have the key, or does the field that results in farther auto populations have to be a the Primary Key?

My trying to find out how to auto-populate data is the whole reason for the original primary key question (the auto-populate problem is still trying to be addressed).

 

[james_lankford]

you don't use full name fields either
for the same reasons you don't use last names

in base_table, make ID an autonumber primary key
in Contacts2, make a long integer field named base_tableID, but not a autonumber primary key

then create a relationship between the 2 ID's

then for autopopulating, you use queries

 

[boblarson]

Re: Do not do it!!!!!!

I was under the impression that you need the key for the auto population though. Meaning:
Say i have Bob Jones, Kate Jones, and Dave Smith. They all have phone numbers. I need my form to select them by name. THIS IS A REQUIREMENT.

Not a problem. In fact it is very easy using a combo box for that. But it doesn't have to be the primary key to do so.

Perhaps I will just create a full name field rather than a first and last to remove duplicate last names.

Not a good thing to do either.

Anyway, I need to select the name, then after the name is selected on the form, there phone number is automatically filled in properly as well into the form. That way when I submit the form later. I can see all that data in the base table (right now seeing there name and number is pretty useless but I end up doing much much more. once i get this fundamental concept down I can really accomplish what I need to).

The thing to remember is you do NOT want to store the extra data in that base table. You can create a "base query" which will then be able to show you the information in a meaningful way and you can use that query in 99% of the places you would use a table.

Are you then implying that if I were to have a full name field rather than a first and last, that any field can have the key, or does the field that results in farther auto populations have to be a the Primary Key?

I'm not saying anything of the sort. What I'm saying is that you need to learn that the primary key identifies a unique row of data. That is all. You can get that data and use it very simply. But you can do it without displaying the key even.

My trying to find out how to auto-populate data is the whole reason for the original primary key question (the auto-populate problem is still trying to be addressed).

Okay, so try to get this concept. You can DISPLAY the data WITHOUT storing it twice.

Take a look at this sample.
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=37842&d=1309272797
This shows you a simple way in which you can DISPLAY the extra data when you select something else from the combo box.

 

[lubbs65]

you don't use full name fields either
for the same reasons you don't use last names

in base_table, make ID an autonumber primary key
in Contacts2, make a long integer field named base_tableID, but not a autonumber primary key

then create a relationship between the 2 ID's

then for autopopulating, you use queries

Okay, so I link the arrow going from "base_table" to "Contacts2" going from ID to base_tableID.
then you had a typo that needs addressing:
is there supposed to be a primary key at all for "Contacts2"? if so what for?
Then what data type should base_tableID be?

Then what to I do?
I need to have a combo box for names. that is a requirement. After they fill in a name. I then need the default text box for Job Title to get filled in.
I do not want the user to fill in base_tableID in the form.

 

[boblarson]

Okay, so I link the arrow going from "base_table" to "Contacts2" going from ID to base_tableID.
then you had a typo that needs addressing:
is there supposed to be a primary key at all for "Contacts2"? if so what for?
Then what data type should base_tableID be?

Then what to I do?
I need to have a combo box for names. that is a requirement. After they fill in a name. I then need the default text box for Job Title to get filled in.
I do not want the user to fill in base_tableID in the form.

See my sample - and it all may become a little clearer.

 

[lubbs65]

Re: Do not do it!!!!!!

Take a look at this sample.
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=37842&d=1309272797
This shows you a simple way in which you can DISPLAY the extra data when you select something else from the combo box.

You are being very helpful so thank you.
There is one problem however. Either I am now understanding how your advice relates to my overall purpose, or you don't understand my overall purpose.

So here is a break down:
Right Now i have a table with people in it.
I also have a table with Codes.

I need to make a 3rd table (or so I believe).
This Table will contain information made by a form.
Every week a user will fill out the form. Maybe this week he will say: "Kelly Jones" has to do Code "X".
Next Week perhaps he will say: "Bob Smith" has to do Code "Y".

Now, Apon filling out Bob Smith it must fill out farther information. His phone number, job title etc. Doing Code "y" or what have you also has parts that follow. and thus I need inheritance. I then need each instruction to get saved every week somewhere though.


This 3rd table is supposed to be used to hold the data with who is supposed to do what. So another field would probably be dates. Then, eventually when the user looks back at the table he would say:
"On 6/29/2011 Kelly Jones did Code X" and would also have the other inherited data.
He would also see:
"On 5/5/2011 Bob Smith did Code Y".

When I was looking at my other file that i based my program off of. In order to do similiar. The query: "base_query" or what have you housed the main table "base_table" which then links to other tables. In my case base_table spreads to "contacts2" and "table_3". The user then opens up the form (which i believe is supposed to be base_query). He then Enters who has to these things. as described in the paragraphs above.

So, right now I am trying to figure out how to create this form. Page 1 has the layout i was trying to use (although the primary keys may be all wrong) but the idea is to populate the table base_table rather than just having a massive query which would then maybe eventually use hundreds of filters.

If i dont include the table base_table into the design of base_query I then have over 140,000 rows. This becomes a huge pain (there are over 5000 codes and a load of employees). So this isn't just a query I can filter through to get the right data. Do you understand? Or is it me who isn't understanding?

 

[boblarson]

Re: Do not do it!!!!!!

You are being very helpful so thank you.
There is one problem however. Either I am now understanding how your advice relates to my overall purpose, or you don't understand my overall purpose.

So here is a break down:
Right Now i have a table with people in it.
I also have a table with Codes.

I need to make a 3rd table (or so I believe).
This Table will contain information made by a form.
Every week a user will fill out the form. Maybe this week he will say: "Kelly Jones" has to do Code "X".
Next Week perhaps he will say: "Bob Smith" has to do Code "Y".

Now, Apon filling out Bob Smith it must fill out farther information. His phone number, job title etc. Doing Code "y" or what have you also has parts that follow. and thus I need inheritance. I then need each instruction to get saved every week somewhere though.

You store ONLY the Code and the ID of Bob Smith. You do not need to include the other data. YOU ALREADY HAVE IT in the table with people in it. So, you would store the Code and the ID of the person. When you need to know what the rest of the information is, you use a query, not just the table. There is

This 3rd table is supposed to be used to hold the data with who is supposed to do what. So another field would probably be dates. Then, eventually when the user looks back at the table he would say:
"On 6/29/2011 Kelly Jones did Code X" and would also have the other inherited data.

I understand that.

When I was looking at my other file that i based my program off of. In order to do similiar. The query: "base_query" or what have you housed the main table "base_table" which then links to other tables. In my case base_table spreads to "contacts2" and "table_3". The user then opens up the form (which i believe is supposed to be base_query). He then Enters who has to these things. as described in the paragraphs above.

You do not include the contacts table nor the codes table in the recordsource for the entry form. It would only be the table 3, not the "base query" which I was talking about. The form would use a COMBO box to select the code (row source would be the codes table, or if you want it sorted, a query of the codes table). The PERSON combo would have as it's record source the persons table (or query on the persons table if you want it sorted) and the column count of the combo set to the number of columns in the table/query and then the column widths set to just display perhaps a concatenated field which makes up the full name of the person just for selecting. The bound column would be the column that the ID is in and it would have its control source be the field in table 3 which is the PERSONID field. So then you can have controls on the form which reference the other columns in the person combo's recordsource like this:

=[ComboNameHere].[Column](1)

(which the 1 denotes the second column of the combo and 2 would be the third and so on as it is zero-based). So you can DISPLAY the phone number, or any other fields from the persons table by use of the combo box. If I could post a file here I would post a specific example for you if you would post your table names and field names of those tables (and perhaps make some suggestions about them). But since I can't, perhaps you might want to go over to Access World Forums, where I had the file sample and then post a thread there where I CAN do up a sample which shows you how YOUR data would work in such a situation as I know it seems difficult to get your head around.

Or is it me who isn't understanding?

Unfortunately it is you.