Creating Unique ID for each record

[joym]

I have a fault lodging system where each fault has a ticket number. this is based on 2 factors Severity category(Service affected or Non Service Affected) and Priority level (Critical,Major,Minor, Planned , Unplanned).

The ticket is manually entered in this format "xxx ##/**".
XXX are 3 letter codes
## are numbers that start from 1 and increment by 1 to x value for every TT Type
** indicates the month .

there are 3 types of tickets
NOC - network operations Critical tickets eg NOC 01/05
NMT - network maintenance Ticket eg NMT 05/04
NSA - non service affecting ticket eg NSA 08/02

The different tickets can have the same numeric code eg NOC 01/05 , NMT 01/05 , NSA 01/05.

If the Service category is service affected then TT code should be NOC ##/**
if the service category is Non service affected and Priority level is Planned Maintenance then it is a NMT ticket
If Service category is Non service affected and Priority level is minor or unplanned then its a NSA ticket

the issue is when we sort it from A to Z or Z to A the its sorted according to the letter that the 1st number eg

NMT 11/05
nmt 12/05
nmt 19/05
nmt 100/05 - should be nmt 20/05 but since 1 comes before 2, it sorts all 100 and 1000 before All TT number that start with NMT 2##/**
nmt 199/05


nmt 20/05

How can i sort it alphabetically then Numerically in descending order

I also have to automate this and i understand the If statements i will need to use and also the Dlookup to check the previous TT code and number. however the how do u auto calculate the TT prefix and number code??

If inverting the code eg 01/05 NMT will solve the issue than i could use that as long as i am a know how to do the calculation.

[June7]

Best practice would be not to create this ID but to save each part into its own field. However, having said that, I have done something like what you want. Requires that the number parts be saved with placeholder zeros, example:

NMT 011/05
nmt 012/05
nmt 019/05
nmt 020/05
nmt 100/05
nmt 199/05


But what should happen when you reach 999?

I do a unique id that follows pattern: YYYYA-####.

Examples:
2011A-0008
2012A-0008

As you can see, the sequence starts over every year and has never exceeded 5000.

[ssanfu]

I agree with June - because this is dealing with a string, if the max TT number is 999, then all entries should be 3 characters.
Notice I said characters and not digits/numbers. Sorting in a text type field is by charaxter, in a number type field it is the value.


There might be another option. Have a query as the record source. Use string functions to separate the the parts of the text field.
Set the order by property.......

[joym]

I agree with June - because this is dealing with a string, if the max TT number is 999, then all entries should be 3 characters.
Notice I said characters and not digits/numbers. Sorting in a text type field is by charaxter, in a number type field it is the value.


There might be another option. Have a query as the record source. Use string functions to separate the the parts of the text field.
Set the order by property.......

Thanks for that really appreciate it however i do not believe that would sort tables in ms access in that format

[ssanfu]

Thanks for that really appreciate it however i do not believe that would sort tables in ms access in that format

Hmmmm, not sure what you mean. All of my forms/reports use queries, never tables. So in the query, you split the ticket number, convert the sequence part and the month to numbers and set the order by property.

The dB I attached in post #3 is doing the sorting....... soooooo???

[joym]

Yes it does the sorting on the form as i would like it to do but i need it to be sorted in that format in the table which has the tickets stored

[ssanfu]

Tables do not have a "Sort order. They are a bit bucket.

To have an "order" in a table, you would have to export the data, delete the records from the table, somehow order the data, then import the data again. Even then, the records in a table are not guaranteed to remain in that order.

Remember, an Access table is NOT the same as an Excel spreadsheet, even though they look similar.

Why does the TABLE have to be sorted?

Users should NEVER edit a table directly!

[joym]

Tables do not have a "Sort order. They are a bit bucket.

To have an "order" in a table, you would have to export the data, delete the records from the table, somehow order the data, then import the data again. Even then, the records in a table are not guaranteed to remain in that order.

Remember, an Access table is NOT the same as an Excel spreadsheet, even though they look similar.

Why does the TABLE have to be sorted?

Users should NEVER edit a table directly!

Users cannot change information on the table however i have given them access to view the tables only and they use sorting to filter certain information.

[ssanfu]

Still, users should never have access to a table; just to view or otherwise.

It is MUCH easier to use a query than a table. Calculations in a query can be done "on-the-fly"; you must add/have fields in a table and update those fields in a table to have the same functionality.

Good luck with your project....