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.

 

[RichardMGreen]

Relationships is under the Tools menu (Access 2003).
If you select it, it will show you any relationships that currently exist between tables.
You'll need to delete the one causing you problems, change your primary key and put the relationship back.

 

[lubbs65]

I have now deleted all relationships and am now getting this error (i finally found the button):

The change you request to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I don't have duplicate data in the last name field, so It has to involve the Index. I don't really understand what an index is or how it works (after reading about it when clicking "help" when getting that error) so if you could lead me into the right direction that would be great.

 

[RichardMGreen]

I would create a new table from scratch with the primary key where you want it and then copy the data across from the original table.
If any fail, you will get a paste errors table that will show you where the problem lies.

You can then delete the original table and rename the new one so any queries that use it are unaffected.

 

[lubbs65]

This is the cause for my error:
The indexed property of a field was changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data is already present in the table.

I have no idea how I could have made this change in the first place so if someone could explain this to me it'd be great.

 

[lubbs65]

I took your advice and created a new table. I exported the original, and ommited the ID number that was orignally default. I imported the new table with the new table back into access. By default it had no key. I set the key to the last name. Upon saving I recieved the identical second error described above this very post.

 

[RichardMGreen]

You might be better off having a compound key.
Try removing the primary key, highlighting first and last names and then putting the key on both.

 

[lubbs65]

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).

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.

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?

Furthermore, if once Smith is selected would there be a filter in place to limit only "Dave" in the first name space. If that's the case then that's fine. However, After selecting "Smith" I don't want the possibility of selecting "John". That was the original idea for why I wanted 1 key.

 

[RichardMGreen]

A compound key will not stop you doing what you've described.
It will, however, stop you having two Dave Smith's or two John Doe's.

 

[lubbs65]

you've been a great help. I really do appreciate it. here is the next step though (im not yet ready to create a new thread if you can answer this problem/maybe any of your hints lead me to figuring it out).

I now have the a query base_query. It will have a form associated with it. Upon Completing the form, it will populate base_query and then base_table.

I am trying to have some auto populates as described above but in a simpler way that I know is possible. I copied a similiar format from another Access file I have so I am pretty sure my design view description is correct. Here is my Design view Window for base_query:

Now, I want to fill out "First Name" and "Last Name" via combo box which i believe are both supposed to be linked to the table "Contacts 2" rather than "base_table" which also obviously contains the same 3 fields of First Name, Last Name, and Job Title. The idea is, once both the first and last names are filled, Job Title is auto populated. In this case, Job Title is a text box. I did add existing fields->base_table->job title. I don't know if job title would have to be under "Contacts 2" rather than base_table in this case. How can I get it to be auto populated though? In the second access file that I am slightly basing my design off of, the fields that should get auto populated are greyed out. Mine obviously are not.

I also have the second linked table that you can see in the design view called table_3. Here I only want one field to be filled out by the user: "code". Respone (was supposed to say response but was a typo twice apparently) is then supposed to be auto populated as well after filling out code.

Respone 2 is selected as a text box taken from add existing fields->base_table->respone.

This may seem redundant in both ideas. This difference however is that for "Job Title" to be auto filled, we should now theoretically require two fields to be filled at first. Respone however only requires 1. I just need to fill out "code" first.

The previous file i am basing my work off of only required one field to be filled before auto filling the remaining. Therefore, I know it is possible to have "Respone" somehow get auto filled. After getting respone, I should be able to find out if and how I can get Job Title to get auto filled.