spectraflame
Well-known Member
- Joined
- Dec 18, 2002
- Messages
- 830
- Office Version
- 365
- Platform
- Windows
I have a form that allows my users to key in a service order. I need a unique letter/number combination assigned to each record or service order depending on the year, department, and numeric sequence.
In my table I have an ID column that is defined as an auto number just to keep track of all of the records. I have another column that is for the service order number itself.
The example below shows the format that I would like to have for my service order number. The first letter is the first letter of the department. It is then followed by 2 digits to note the current year, then it is followed by the next number in the sequence of service orders for that specific department. My departments are: Electric, Water, Sewer, Maintenance, Locate, & Other.
E04 - 00100
W04 - 00100
S04 - 00100
E04 - 00101
W04 - 00101
S04 - 00101
M04 - 00100
L04 - 00100
The reason that I have for keeping the numeric sequence for each department is for internal catalog and reporting.
I have thought about separate tables, but they may get a little tricky for me to figure out my reporting needs. I just thought that keeping all of the records in a single table would be better.
Any thoughts?
Matthew
In my table I have an ID column that is defined as an auto number just to keep track of all of the records. I have another column that is for the service order number itself.
The example below shows the format that I would like to have for my service order number. The first letter is the first letter of the department. It is then followed by 2 digits to note the current year, then it is followed by the next number in the sequence of service orders for that specific department. My departments are: Electric, Water, Sewer, Maintenance, Locate, & Other.
E04 - 00100
W04 - 00100
S04 - 00100
E04 - 00101
W04 - 00101
S04 - 00101
M04 - 00100
L04 - 00100
The reason that I have for keeping the numeric sequence for each department is for internal catalog and reporting.
I have thought about separate tables, but they may get a little tricky for me to figure out my reporting needs. I just thought that keeping all of the records in a single table would be better.
Any thoughts?
Matthew