Monthly Unique Sequential Number

[iamtrying]

Hello,

I have a project requiring a unique sequential number for the month. We are trying to generate accident control numbers for two facilites. For example, the first digit indicates the facility. It will always be "C" or "R". The next six digits will be the accident date not the system date. There is a field in the table where the accident date is entered.

The last two digits will be the sequential number of each accident for the month. At the beginning of a new month the sequential number must reset to 00.

Is there a method to ask the user what is your facility C or R and what is the accident date? The answer is collected and the accident control field is populated with the unique sequential number? Below is the desired numbering.

C04130901
C04130902
C04130903
C04130904
R04130905
C04130906
R04130907
R04130908

Thanks

 

[lameid]

You are packing data into a field which is not proper design especially for a unique field.

You would store the facility and accident date and have another field that is unique, likely an autonumber. This is part of good database design called data normalization.

I guess then you could assign sequential numbers for the accidents then. But my question would be why? I would instead store a date and time of the accident. That would be more useful information.

 

[iamtrying]

The date and time is currently stored in the table for both facilities. However, from past practices, the accident control number has been a combination of the tpye of accident, date of accident, sequential number, and facility ID. I am open to your suggestions.

 

[lameid]

I would use an autonumber field as the primary key for your accidents. Then when you display it you can also throughout your database always display the date and facility of the accident. This gives everyone all the infomation they are used to seeing.

You usually end up with multiple fields crammed into one like that when you can not change the structure of the database or control the reporting. In order to make a canned system or database that can't easily be modified work, people do that. It is bad design. Asking for all C facilities is a lot faster than looking for control numbers that start with C. For that matter, looking for all 1 facility_id's is faster than looking for facility C. Exact matches are faster and number matches are faster than text. And fastest matches are always done on indexed fields.

The good thing about a separate accident date field is that if the wrong date is entered, it is easliy changed without upsetting the accident number. Accident 1 is always accident 1. IF R04130908 really happened on 4/12/2009; what then?

 

[iamtrying]

So are you suggesting to make the default new record field for the facility C or R respectively, place the date of accident next to it, and place the autonumber field next to the date of accident?

Okay, what about resetting the autonumber monthly?

 

[lameid]

I would not reset the autonumber. I don't know why that would be a requirement.

 

[iamtrying]

I hope someone can help me with a problem I have encountered with the suggested solution. There are three tables within the database (Accidents, Injuries, & Vehicles). The PKs are autonumber fields accrecno, injrecno, & vehrecno in each respective table. The problem occurs when there are more than one injury or vehicles involved in an accident. I am searching for a unique identifier which will link one accident to many injuries and/or vehicles. I have tried linking the tables on accident date, but if there are more than one accident on the same date, the data is linked to the wrong record based on the date. Each time a record is added to the injury or vehicles tables, the autonumber increases and does not match the accident autonumber. Any suggestions?

 

[lameid]

Use accrecno as your foreign key from injuries and vehichle tables to match up to your accident table... In the case of vehichles you may need antother table that has both accrecno and vehrecno because a vehichle could end up being in multiple accidents eventually. Obviously each injury is its own incident and this would not be the case.

 

[PHV]

Have a look here:

http://r937.com/relational.html

there are more than one injury or vehicles involved in an accident
You probably want accrecno as FK in Injuries & Vehicles.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iamtrying]

Thanks guys I am on it.

 

[iamtrying]

Okay, I redesigned the database after reading the linked article posted by PHV and the field linking advise given by lameid. I now have one-to-many relationships on the injured and vehicles tables and the reports are returning the correct data!!

Thanks guys for the solid advise,
Iamtrying