Sorting alphanumeric values

[julostarr]

I am currently using Access 2003 to create a database for my company. One of
the fields in a table has our part numbers, which contain both numbers and
letters for example 21BC123, so I left it as a text field because I didn't
think that a numeric field would allow text. The part numbers sort correctly
when I sort in the part numbers table, but they sort in a weird order in my
queiry and report. For example:

20D10-3
21BC123
21D10
22D10
25TD47
21FA101
25FA203
38FA601
21FP604
38WS100

I'm not sure how exactly they are being sorted, but the order should be
first by number then by letter then by number again. Like this...

20D10-3
21BC123
21D10
21FA101
21FP604
22D10
25FA203
25TD47
38FA601
38WS100

Is there any way to fix this?

 

[BruceM]

It is not usually necessary to post to more than one group, but if it is the
way to do that is to put all of the groups in the Address line of a single
message (cross-posting) rather than sending essentially identical messages
to several groups. This seems to be the same question as in the Access
group.

 

[Douglas J. Steele]

The order in which your table shows the data doesn't actually have any
bearing on the order in which the data is stored.

For a form, you need to create a query that has the appropriate ORDER BY
clause in it, and use the query as the form's RecordSource rather than the
table.

For a report, you must use the Sorting and Grouping dialog to set the order,
even if the report's based on a query that has the appropriate ORDER BY
clause.

 

[julostarr]

Didn't realize it got posted twice. I couldn't find it the first time I
posted so I thought it wasn't there. That is why there are 2.

 

[julostarr]

I'm relatively new at this, but I did use a query to make the report. The
original table sorts the part numbers correctly, but The query does not
sort properly either. It's not an issue of ascending or decending order.
I'm not sure what kind of order my part numbers are being sorted in. For
example the 25TD47 comes before the 21FA101 but after the 21BC123 (with other
numbers in between). These should be in order like this:

21BC123
21FA101
25TD47

 

[Douglas J. Steele]

What's the SQL of the query?

(and remember that I said for the report you must set the sorting in the
Sorting and Grouping dialog: that any ORDER BY clauses in the query will be
ignored by the report)

 

[julostarr]

The sorting is set in the report separately and as I said some are sorted
correctly,

For instance...

21BC123
21BC124
21BC125

but others are in the wrong places as I showed before.


I'm not sure what SQL means. Please explain.

 

[Douglas J. Steele]

Regardless of how you created the query, Access converts it to SQL behind
the scenes. To see the SQL associated with your query, open the query in
Design view, then select SQL View from the View menu.

 

[julostarr]

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced, Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;


What do I need to do?

 

[Douglas J. Steele]

Change what's after the ORDER BY key word to be whatever field it is that
contains the values 21BC123, 21BC124, 21BC125 etc.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;


What do I need to do?

Regardless of how you created the query, Access converts it to SQL behind
the scenes. To see the SQL associated with your query, open the query in
Design view, then select SQL View from the View menu.

 

[John W. Vinson]

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced, Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;

Take a look at the last line:

ORDER BY [Shipping Information].ShipMonth;

or equivalently, look at the Sort row on the query design grid.

You're asking Access to sort the records by the value in ShipMonth (so you'll
see all January records together, then all February, then all March, etc; or
perhaps all April records, then all August records, etc. depending on what's
in ShipMonth).

If that's not what you want, change the Sort line on the query grid, or
(again, equivalently) specify the field or fields that you want in the ORDER
BY clause.

 

[julostarr]

It looks like it is already correct. Is there anything else I can do? It
just is not right. I want to use this instead of excel to do reports on our
booked sales, but it will be hard to go completely over to it if it doesn't
sort correctly.

Change what's after the ORDER BY key word to be whatever field it is that
contains the values 21BC123, 21BC124, 21BC125 etc.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;


What do I need to do?

Regardless of how you created the query, Access converts it to SQL behind
the scenes. To see the SQL associated with your query, open the query in
Design view, then select SQL View from the View menu.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The sorting is set in the report separately and as I said some are
sorted
correctly,

For instance...

21BC123
21BC124
21BC125

but others are in the wrong places as I showed before.


I'm not sure what SQL means. Please explain.

 

[KARL DEWEY]

In your other post I suggested that you check for leading spaces. Then use
the trim function.
Some_Name: Trim([YourFieldName])
--
KARL DEWEY
Build a little - Test a little

It looks like it is already correct. Is there anything else I can do? It
just is not right. I want to use this instead of excel to do reports on our
booked sales, but it will be hard to go completely over to it if it doesn't
sort correctly.

Change what's after the ORDER BY key word to be whatever field it is that
contains the values 21BC123, 21BC124, 21BC125 etc.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;


What do I need to do?

:

Regardless of how you created the query, Access converts it to SQL behind
the scenes. To see the SQL associated with your query, open the query in
Design view, then select SQL View from the View menu.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The sorting is set in the report separately and as I said some are
sorted
correctly,

For instance...

21BC123
21BC124
21BC125

but others are in the wrong places as I showed before.


I'm not sure what SQL means. Please explain.

 

[John W. Vinson]

It looks like it is already correct.

Not if it's what you posted! That query is sorting by ShipMonth, not by Part
Number.

 

[julostarr]

I changed it and now it looks like this:

SELECT [Shipping Information].ShipMonth, Orders.CustomerID, [Order
Details].ProductID, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details].Price, Orders.[Job#],
Orders.Invoiced, Orders.PurchaseOrderNumber, [Shipping
Information].ShipDateChg1, [Shipping Information].ShipDateChg2, [Shipping
Information].ShipDateChg3, [Shipping Information].FinalShipDate
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Order Details].ProductID;


....but it still is sorting the same way.

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced, Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;

Take a look at the last line:

ORDER BY [Shipping Information].ShipMonth;

or equivalently, look at the Sort row on the query design grid.

You're asking Access to sort the records by the value in ShipMonth (so you'll
see all January records together, then all February, then all March, etc; or
perhaps all April records, then all August records, etc. depending on what's
in ShipMonth).

If that's not what you want, change the Sort line on the query grid, or
(again, equivalently) specify the field or fields that you want in the ORDER
BY clause.

 

[BruceM]

Is ProductID in the Order Details table the field that contains the part
number?

I changed it and now it looks like this:

SELECT [Shipping Information].ShipMonth, Orders.CustomerID, [Order
Details].ProductID, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details].Price, Orders.[Job#],
Orders.Invoiced, Orders.PurchaseOrderNumber, [Shipping
Information].ShipDateChg1, [Shipping Information].ShipDateChg2, [Shipping
Information].ShipDateChg3, [Shipping Information].FinalShipDate
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Order Details].ProductID;


...but it still is sorting the same way.

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping
Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping
Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;

Take a look at the last line:

ORDER BY [Shipping Information].ShipMonth;

or equivalently, look at the Sort row on the query design grid.

You're asking Access to sort the records by the value in ShipMonth (so
you'll
see all January records together, then all February, then all March, etc;
or
perhaps all April records, then all August records, etc. depending on
what's
in ShipMonth).

If that's not what you want, change the Sort line on the query grid, or
(again, equivalently) specify the field or fields that you want in the
ORDER
BY clause.

 

[julostarr]

Yes it is.

Is ProductID in the Order Details table the field that contains the part
number?

I changed it and now it looks like this:

SELECT [Shipping Information].ShipMonth, Orders.CustomerID, [Order
Details].ProductID, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details].Price, Orders.[Job#],
Orders.Invoiced, Orders.PurchaseOrderNumber, [Shipping
Information].ShipDateChg1, [Shipping Information].ShipDateChg2, [Shipping
Information].ShipDateChg3, [Shipping Information].FinalShipDate
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Order Details].ProductID;


...but it still is sorting the same way.

On Wed, 13 Feb 2008 13:12:02 -0800, julostarr

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping
Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping
Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;

Take a look at the last line:

ORDER BY [Shipping Information].ShipMonth;

or equivalently, look at the Sort row on the query design grid.

You're asking Access to sort the records by the value in ShipMonth (so
you'll
see all January records together, then all February, then all March, etc;
or
perhaps all April records, then all August records, etc. depending on
what's
in ShipMonth).

If that's not what you want, change the Sort line on the query grid, or
(again, equivalently) specify the field or fields that you want in the
ORDER
BY clause.

 

[BruceM]

When you run the query by itself (rather than opening a form or report that
uses the query as its record source) does it sort correctly?
If not, one thing to check is whether ProductID is a lookup field (in table
design). If so, that can cause unexpected results when attempting to sort.
Other things to try include compacting and repairing the database,
re-creating the query, or creating a new blank database into which you
import all of the objects (except this query) from the current database. If
you take this route it may be best to re-create the query.

Yes it is.

Is ProductID in the Order Details table the field that contains the part
number?

I changed it and now it looks like this:

SELECT [Shipping Information].ShipMonth, Orders.CustomerID, [Order
Details].ProductID, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details].Price, Orders.[Job#],
Orders.Invoiced, Orders.PurchaseOrderNumber, [Shipping
Information].ShipDateChg1, [Shipping Information].ShipDateChg2,
[Shipping
Information].ShipDateChg3, [Shipping Information].FinalShipDate
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Order Details].ProductID;


...but it still is sorting the same way.

:

On Wed, 13 Feb 2008 13:12:02 -0800, julostarr

Ok, I looked it up. My SQL is as follows...

SELECT Orders.CustomerID, [Order Details].ProductID, [Order
Details].Quantity, [Shipping Information].OriginalShipDate, [Order
Details].Price, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber,
[Shipping Information].ShipDateChg1, [Shipping
Information].ShipDateChg2,
[Shipping Information].ShipDateChg3, [Shipping
Information].FinalShipDate,
[Shipping Information].ShipMonth
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Shipping Information].ShipMonth;

Take a look at the last line:

ORDER BY [Shipping Information].ShipMonth;

or equivalently, look at the Sort row on the query design grid.

You're asking Access to sort the records by the value in ShipMonth (so
you'll
see all January records together, then all February, then all March,
etc;
or
perhaps all April records, then all August records, etc. depending on
what's
in ShipMonth).

If that's not what you want, change the Sort line on the query grid,
or
(again, equivalently) specify the field or fields that you want in the
ORDER
BY clause.

 

[John W. Vinson]

I changed it and now it looks like this:

SELECT [Shipping Information].ShipMonth, Orders.CustomerID, [Order
Details].ProductID, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details].Price, Orders.[Job#],
Orders.Invoiced, Orders.PurchaseOrderNumber, [Shipping
Information].ShipDateChg1, [Shipping Information].ShipDateChg2, [Shipping
Information].ShipDateChg3, [Shipping Information].FinalShipDate
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Order Details].ProductID;

Is ProductID a Lookup Field in the OrderDetails table? If so it's sorting by
the concealed numeric autonumber value rather than by the text product ID.

 

[julostarr]

Yes, it is a look up field. So that must be the problem. I have lots of
information in the database right now. I'm sure that new products will be
added to the Product table in the future. So if I add the new products at
the end of the Products table and not try to put them in the order they
should be in alphanumericly then they will always be out of order in reports
and queries? I could go in and reorder them in the Products table, but won't
that change the part ordered in my orders table if that part is on order?

I changed it and now it looks like this:

SELECT [Shipping Information].ShipMonth, Orders.CustomerID, [Order
Details].ProductID, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details].Price, Orders.[Job#],
Orders.Invoiced, Orders.PurchaseOrderNumber, [Shipping
Information].ShipDateChg1, [Shipping Information].ShipDateChg2, [Shipping
Information].ShipDateChg3, [Shipping Information].FinalShipDate
FROM (Orders INNER JOIN [Order Details] ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
ORDER BY [Order Details].ProductID;

Is ProductID a Lookup Field in the OrderDetails table? If so it's sorting by
the concealed numeric autonumber value rather than by the text product ID.