Resetting Autonumber

[kirnavi]

Hi - I am running an audit of our database once a month and am trying to create a unique ID number for each participant. This audit looks for deceased people and returns 'hits'. So out of 95000 people, maybe 100 die. The next month, I need to send out the 94900 people from the previous audit, as well as all new participants.

Here's what I did: I created an AutoNumber field and then made the control number field (the date plus the autonumber). What I need is for each time I ran that macro, the AutoNumber resets to zero so the first person with the new control number would be, for instance, 20020315000001 and so on.

I have tried the Access help idea of creating a temp table and appending the number to my destination table. I named the one field in the temp table the same as my dest table and entered '0', ran the append query, and then the rest of the macro. The second entry in the destination table just picked up from 95000 rather than 0. ??? Did I do something wrong?

Thanks,
Susan

 

[Bastien]

Why don't just create a field with the date of death or a boolean field that gets checked once the participant dies. You'll have to find some way of updating the field. Then just run a query that asks for all participants who are not deceased as indicated by a date in the DOD field or a check in the deceased boolean field. Way simpler and much quicker to run and no need to muck about with Autonumber resets.

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[kirnavi]

We run the audit based on all living people and then send the data to a vendor, who returns all deceased people. That's where I need the unique number (you would think a Social Security Number would be enough but it's not) so I can match up our original data to the returned info.

SO, just realized that cleaning out my table and compacting the database will reset the AutoNumber to zero. Is there no way of automating this in a macro? Why does 'Tools -> Database Utilities -> Compact Database' work but 'RunCommand -> CompactDatabase' in a macro does not?

 

[kirnavi]

We run the audit based on all living people and then send the data to a vendor, who returns all deceased people. That's where I need the unique number (you would think a Social Security Number would be enough but it's not) so I can match up our original data to the returned info.

SO, just realized that cleaning out my table and compacting the database will reset the AutoNumber to zero. Is there no way of automating this in a macro? Why does 'Tools -> Database Utilities -> Compact Database' work but 'RunCommand -> CompactDatabase' in a macro does not?

BTW, I am using Access 97.