I'm confused with this, help!!!

I have a small program for a clinic, and have a very small problem dont know how to cope with it. When a new Patient walks in to the Clinic, his account is created and an unique ID is allocated to him, that unique ID is the autonumber field. Now lets say a patient does not appear for 6 months and they delete his Account details...so there will be an empty space. To explain myself clearly, what I'm saying is: Lets say I walk into the Clinic and open an Account- and I get an ID number 64...then I stop going to the clinic and the adminstration people delete my account. So if the clinic has 2000 Patients then the ID number 64 will be empty, what I wish to do is...when a new patient walks in, it checks for empty spaces before allocating a new number and if there's any empty ID available then that be allocated to the new patient. I hope you get me right...

My experience is AutoNumber (in access anyway) doesn't let you go back. All new records are +1 from the previous one.

Using the 'autonumber' field won't allow you to do this. So you'd have to change it to a regular Long data type and manually update the value each time a new patient is added to the system.

And as far as I know, it makes sense to use numbers in sequence. Using a value that has been previously deleted from the database is not really good database programming in my opinion. It's best to use a new unique value which has never been used before. This guarantees you will have no conflicts with child records etc.

If you are insistent on filling the 'holes' in your ID field, then what you COULD do is add a table that stores all the deleted ID's.

So when you delete a patient from the system
1) Add the ID of this patient to the DeletedID table

When you add a patient to the system
1) Check to see if any records exist in the DeletedID table
a) If so, get the minimum value (SELECT Min(ID) FROM DeletedID), then delete this value from the DeletedID table
b) If not, simply assign the next logical value in sequence

This is probably the fastest way to do it, when you have thousands of records.

Hope this makes sense

AE is right!

I usually add a boolean field for Active/inactive and then just add the appropriate filters to my app. You really never want to delete data, just hide it.

Plus then you can do reporting on clients who left the practice!

hehe

Thanks AE I will let the holes be there coz you are right that way atleast there will be no conflicts. But could you tell me, how many records can a Table in Access store. The people I'm writing this software for, have 5,000 patients already and more will come...so can Access hold all that data...in one Table...

At my job we have tables with over 30,000 records, so you are good to go!