autonumber primary key - problematic

[stellJess]

hi there!

i've inherited an access2000 database that is performing poorly. while it has a primary key for each table w/foreign keys as appropriate, the primary keys are autonumber and randomly generated. perhaps this is the cause of the poor performance. one patient can have multiple unique id numbers b/c the id is generated by access. and, and there is no way to refer to that number again b/c it is not presented to the user at any time. the result, duplicates!

one more thing, in the one-to-many relationship, the id (patient's numerical id) on the one side is of type number that relates to the id on the many side. the id on the many side is of type long integer that is displayed as text (patient's name). this is very confusing b/c of a type misMatch. ???????

can someone out there help and tell me how to go about fixing this. the database has 12 tables set up this way. i do not know where to begin.

anyAny help is greatly appreciated.

stellJess

 

[jebry]

Hi stellJess!

First you need to determine what duplicates have occurred and what related records there are. Then make sure all of the related records point to the same patient record. Then you need to delete all of the superfulous records. Finally, you need to code validation and search options into the database so no more duplicates can be added and any patient can be found to add new information. If the patient has an ID number that the office uses, then that number can be used as the PK, but that will take some work to enter (you can use the cascade update to fix related records). If no unique ID is assigned generally, then leave the autonumber alone. I think that the extra programming required to deal with the autonumber is better than storing the patient's name and phone number in each related record.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Darrylles]

Hi Stell,

The duplicate patients are probably because when a patient appears, they are automatically entered into the database.

Is there not a feature to search for current or past patients?

Jebry's answer is essential to rectify the current state of the tables, but they will continue to occur unless rectified at the 'data entry' point.

Regards,

Darrylles "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[stellJess]

Thanks!!

i have proceeded to correct the problem according to Jerby's prescription. andAnd, you are correct, i should include a search feature at the point of data entry -- i will do it.

i am sure i will need guidance again. so stay tuned...

again, thanks for all your input.

stellJess