Numbering Records

[euphonium01]

I'm certain the question of numbering records is an old topic, but I think my numbering issue could be a little different as records are Alpha / Numeric records. Records are not Unique but (although necessary) duplicates are rare?



I have a field that identifies a particular widget and its location, say DA52-1. This tells me it is loctaed in Isle 'D', on shelf 'A', and the widget is a type 52-1. The next widget may not be perfectly sequential for instance, it could be DA51-3 then DA52-4 etc. The problem is DA52-11 will display before DA52-2 or DA52-5.

I need to sort the records so that this field is how we would read it or write it in order, DA52-1, DA52-2 through to DA52-9, then DA52-10 -11 -12 and so on. DA is not always prevelant, it could be EM5-1, but again, EM5-10 will display before ID EM5-2. All records have the widget ID consisting letters first, followed by number(s), a dash, then number(s).

Is there a way I can correct this at the Table level, or is a Query required? Or another way?

I am OK with vba if that is required s'long as it's not too complicated Putting records in the correct order and then adding an Autonumber field to maintain that order isn't the answer for my particular needs.
Paul

[Micron]

I don't see any other way but to split the prefix text field (DA) from the suffix numeric field and group and sort by prefix and sort by number. What you're experiencing is what happens when you sort numbers as text data type. You can run an update query to split existing data into 2 new table fields but I'll bet this causes some kind of cascading re-designs. Then try something like

SELECT tblSorting.Prefix, tblSorting.Num
FROM tblSorting
GROUP BY tblSorting.Prefix, tblSorting.Num
ORDER BY tblSorting.Prefix, tblSorting.Num;

You'll get this

Prefix	Num
DA	1
DA	2
DA	5
DA	11
DA	55
DA	111
EM	2
EM	3
EM	11
EM	22

You concatenate a string that you want to see in your form or query: [Prefix] & "-" & [Num]
Could also do it in the sql that I posted. This will also make it MUCH easier to figure out the next num for a prefix should that be something you want to do.

[Welshgasman]

Could you not just split at the -?
So
DA52 and 11
DA52 and 2
EM5 and 11
EM5 and 2

Try that, but see where DA5 and 11 comes into the mix

Most times, that data would be split anyway, so that you have fields called Isle, Shel and WidgetType.
Then you would combine to get what you are used to seeing. Easier to combine, than to split normally.
Though you could create a dedicated function for the split.?

[ArviLaanemets]

To make ordering by such text fields possible, instead 'DA52-1','DA52-5','DA52-10','DA52-111', etc. use someting like 'DA52-001','DA52-005','DA52-010','DA52-111' , etc.. The number of leading zeros must be do the length of this character group will be same for all records.