Trying to change data type from number into Autonumber

[Cal3242]

I am working on making a database for schoolwork and i was asked in corrections to change the data type from number to autonumber because when i go into design view and try to enter my records (for a dental practice eg. client details)
it would automatically enter the next number for the Appointment ID (1,2,3,4 etc.) but when i try this for each of my foreign keys in the table (client ID, employee ID, payment ID and Treatment ID) a notification comes up saying "you cannot change the data type or field size of this field; it is part of one or more relationships" it says i must delete the relationships to do this? i tried this already in "database tools" section but i haven't got a clue! can someone help please!? i am using Microsoft Access 2007 Thanks

Cal :)

[PhilOfWalton]

Basically, you can't.

Roughly speaking having back up your database -just in case....
you need to create an Autonumber field in every table where there isn't one, and set that as the primary key. Then create a new field in the main table - Type numeric - long for all the related tables.

So I'm presuming Appointments is your main table and Clients, Employees, Payments & Treatments are your are your related tables.

So those 4 tables will have a new Autonumber prime key called
NewClientID, NewEmployeeID, NewPaymentID & NewTreatmentID.

The Appointments Table will have a new Autonumber prime key called NewAppointmentID.

Open each table and the New Automumber fields will automatically be populated.

Now create 4 update queries with Appointnents and 1 of the other 4 tables.

So assuming that your first update query is with Appointments & Clients.

You say you already have a relationship established between the old ClientID from the Client table and the old ClientID in the Appointments table. So you need to update the NewClientID in the Appointments table to the NewClientID from the Clients table.

Do that for all 4 update queries.
Delete the existing relationships
Add the new relationships between the New... in each of the tables.
CHECK VERY CAREFULLY that all is OK.
Delete the redudand old fields in all 5 tables

Good luck

Phil