J
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?
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?
BruceM said: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.
julostarr said:Yes it is.
BruceM said: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.