Auto-numbering a field incrementally

[Yatiman Idara]

I am doubting whether what I am about to propose is even possible but here goes nothing.

I have a DB that records applications from all across the country. These are registered with the ID number (autonumber) but management have also come up with strange resolution.

They want to number the applications according to states and counties.

Let me clarify

They don't propose renumbering the ID field.

Lets say I have 100 entries from 14 states across the country. Now obviously the entries are all mixed up so an applicant from NY might be recorders under ID 3, then 10, then 14 and so on.

What I am proposing is making another field, then possibly querying to get all the applicant's from NY and then incrementally filling up that County Number field with these names.


Does anyone have any ideas how I might be able to achieve this?

 

[Gasman]

I would probably do that by having a table that holds the last or next available number for the state/county. When the record is added increment that field.?

I would however question what benefit is obtained by doing this?
If they want to see some sort of sequence, perhaps it could be done on the reporting side?

 

[plog]

You don't really have to store the last number of a series--you can calculate it using DMAX:

http://www.techonthenet.com/access/functions/domain/dmax.php

Use it along with the appropriate criteria to get the last number used for a series, add one to it.

With that said, what happens when someone deletes #46 are the guys in charge going to get into a tizzy because the report jumps from 45 to 47?

 

[MarkK]

What I am proposing is making another field, then possibly querying to get all the applicant's from NY and then incrementally filling up that County Number field with these names.

But, if I understand correctly, that is a duplication of existing information, which is a waste of time. The applicants from NY are already known to be from NY based on existing data. So if you add a field and regroup all your NY applicants, now you have two fields that do the same job.

That's like building a roof over your existing roof, so you more than double your roof-making effort, but you won't stay any drier. This is the definition of wasted effort.

 

[Yatiman Idara]

But, if I understand correctly, that is a duplication of existing information, which is a waste of time. The applicants from NY are already known to be from NY based on existing data. So if you add a field and regroup all your NY applicants, now you have two fields that do the same job.

That's like building a roof over your existing roof, so you more than double your roof-making effort, but you won't stay any drier. This is the definition of wasted effort.

To be perfectly honest with I am no DB expert and just jumped into this (i.e. My first DB project). The management I am working with (don't ask why) have no idea what a DB is and what its normalization rules are.

So between the two of us (i.e me and management) we have created a DB that will possibly horrify you sensible DB experts

To give you and example, my DB contains two main tables. One has 59 fields and the other has 57.

As to the original issue. So far I have adopted the following approach.

I decided not to store the county number but simply show it in my report. To achieve this I used 'Group and Sort' function in report to set my criteria and added an unbound field with a control source of '=1' and a running sum of 'over group'.

So far seems good to me.

Let's hope our DB savvy management approve