Sorting Records with hyphen

[terryh70]

I have records that are sorted by a text field that looks like this.

MPI-0001-2005
MPI-0002-2005
MPI-0003-2005
MPI-0004-2005

The first four numbers are numerical record numbers, the second four numbers
are the current year.

Now that I have started a new year they no longer sort the way I would like
them to.

MPI-0001-2005
MPI-0001-2006
MPI-0002-2005
MPI-0002-2006
MPI-0003-2005
MPI-0004-2005


I would like them to sort this way;

MPI-0001-2005
MPI-0002-2005
MPI-0003-2005
MPI-0004-2005
MPI-0001-2006
MPI-0002-2006

How do I do this?

 

[Ofer]

Try this

Select * From TableName
Order By Val(Right(FieldName,4)), FieldName

 

[Bob Miller]

This may be the "dirty" way to do it:
Let's call the table Sorttxt and the field sorter. Create this query:
SELECT Sortertxt.Sorter, Right([Sorter],4) AS SortYr, Left([Sorter],8)
AS SortNum
FROM Sortertxt
ORDER BY Right([Sorter],4), Left([Sorter],8);

 

[John Vinson]

I have records that are sorted by a text field that looks like this.

MPI-0001-2005

How do I do this?

You're having trouble because you are storing two (or is it three? is
MPI a constant?) discrete pieces of information in one field. This is
a violation of normalization principles - fields should be "atomic",
having one and only one value.

I'd suggest storing the year in one field (don't name it Year though,
that's a reserved word) and the sequential number in a different
field. You can make the two (or three?) fields a joint multifield
Primary Key, concatenante them for display purposes, and sort by year
and then by sequence number to get the effect you want.

John W. Vinson[MVP]

 

[terryh70]

This seems like the best solution the only question left for me is how do
i do it. Step by step if possible.

 

[John Vinson]

This seems like the best solution the only question left for me is how do
i do it. Step by step if possible.

1. Open your Table in design view.
2. Add a new field named KeyYear, datatype Number... Integer.
3. Add a new field named KeySeq, datatype Number... Integer.
4. If the MPI is always MPI, never anything else, you're done;
otherwise add a new field KeyPrefix, datatype Text, width 3.
5. Run an Update Query; update KeyYear to

Right([YourField], 4)

and KeySeq to

Mid([YourField], 5, 4)

and (if necessary) KeyPrefix to

Left([YourField], 3)

Check to see if the data all transferred correctly.

Do you have other tables containing this field, using it as a link
field? If so, you'll need to do the same in each of the related
tables, then open the Relationships window; select the join line on
the current field and delete it; open the main table in design view
and ctrl-click on KeyYear, KeySeq (and if necessary KeyPrefix), and
click the Key icon; go back to the Relationships window and join
KeyYear to KeyYear, KeySeq to KeySeq, and (again if needed) KeyPrefix
to KeyPrefix in each dependent table.

You'll need to use a calculated field in a Query such as

"MPI-" & Format([KeySeq], "0000") & "-" & Format([KeyYear], "0000")

to display your current appearance of the key.

John W. Vinson[MVP]