Sorting alphanumeric values

[julostarr]

No, it doesn't sort correctly. It must be because it's a look up field. Is
there any way to fix this with out having to reorder my Products table? I
know I will add new product in the future as they come available so
reordering might be a problem. Is there a way to just reorder the lookup
field and not the Products table?

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.

 

[julostarr]

Thank all of you for your help! I figured it out. I changed the field that
I used in the query to the Product Name itself instead of the ProductID.
This way it is sorting it by the name instead of the number.

I really appreciate it! Thanks!

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]

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?

STOP.

You're still thinking "order a table".

A Table HAS NO ORDER. Think of a table as an unordered "bucket" of records.
Specifying a sort order on a table affects *that display* of the records in
the table; it does not affect the order in which the records are stored, nor
does it affect the order of any query, form, or report based on that query!

The Lookup Field is at fault here. It's annoying, misleading, and has VERY
little value; this is just one of many examples of the problems it can cause.

Open your Query. Select the Products lookup table and join it to the order
table (rather than using the lookup field). Select the product ID (the text
one, not the autonumber!!) from the Products table into the query, and use
THAT in your sort.