Apply an AutoNumber Sequential Value to specific records

[jpvonhemel]

Hello,



I am trying to think of a solid way to number some records based on a screening process. I import all emergency department visit accounts into a table and screen a text field for traumatic injury strings. Account number is my primary key, so I do not end up with duplicates and I can delete records that have not discharged and pull them in with a later import.

Once I identify a patient that meets our selection criteria ie 4000 or 4001, for the Trauma registry, I want to assign them a separate unique number. I thought about using an append table, but the Autonumber field incremented even when a record did not meet the criteria for the new table, giving me gaps in the numbering.

Any hints or ideas how I can create a query to apply a sequential and unique number for records with a specific field value and ignore records I have already assigned numbers to, or have ruled out from the registry.

Thank you,

Jerold

[orange]

Readers need more info about your requirements in order to give more focused responses.
See this for what autonumbers are and are not
I recommend you build a data model based on your requirements using pencil and paper; then test the model with test data and scenarios; get the model working then move to physical data base.

Good luck with your project.

[Micron]

The link is a good one, but I'd like to add that rarely is it that important to have a rock solid sequential numbering system. Having to worry about unique or sequential numbers for different patients suggests you have a design problem - mostly about normalization. Patient data should be one record per, and it matters not if those numbers are sequential but the patient ID (whatever that is) should be in a unique index if not the primary key. Patient visits would be a different table with multiple records of a patient ID allowed, but perhaps only one visit ID allowed for a particular visit. I'm going to presume you'd benefit from some normalization info and possibly other stuff too. These would be a start, but you might find info that's more to your liking if you research the topics. It's easy to create in Access, but easy to end up with bad design as well.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html