Problem with ORDER clause in Access Query?

[Globestar]

Well folks, I have really racked my brain on this problem with Access. (That probably means that I’ve either done something really silly or careless.) No matter, I’m stuck so I figured I resort to this forum of wizards.

I have a table called LEDGER which has NOT NULL PRIMARY KEY = Key and contains transactions data on about text variable called ‘Sym’. Another text variable called TC (for transaction code) contains a unique identifier for each ledger transaction. TC will appear once when a transaction is opened and again when it is closed. (That is, the value in contained in TC can only appear in the ledger a maximum of 2 times -- for closed transactions. Of course, it appears only once for open transactions).

The value contained in ‘Sym’ is a product code. It can take many months from the time an order (for a product) is opened until the time it is closed. Thus, for any one product code, the LEDGER contains many entries, some open and some closed.

Every time a customer places an order for a specific product (Sym) he is assigned a unique TC when the order is opened. Weeks later, when the order is completed, the TC is closed. Thus, the value contained in TC field will appear at most 2 times in the TC field, and only once for open orders.

For each product code (Sym), I needed a query that produced a list for each TC showing the opening date, closing date and the ledger key corresponding to each event. I did this by linking a copy of the table (T1) to itself and joining the TC’s with only the Keys that were not equal. This query is shown below:

SELECT Ledger.Worksheet AS WorkShtR, Ledger.Sym AS SymR, Ledger.TC AS TCR, Ledger.Date AS DateOpen, T1.Date AS DateClose, Ledger.Key AS KeyO, T1.Key AS KeyC
FROM Ledger LEFT JOIN Ledger AS T1 ON (Ledger.TC=T1.TC) AND (Ledger.Key<>T1.Key)
WHERE (((Ledger.Trans) Like "Open"))
ORDER BY Ledger.Sym, Ledger.Date, Ledger.Key;

The query depends on the fact that for every transaction there will always be value of “Open” in a field called ‘Trans’ for every transaction. The query is called Register

The query works fine and produces the desired result. Transactions that are still open have a null value in the closing date (DateClose), which is perfect. Although the table (LEDGER) is organized by increasing values of the key, the output from the query seems to bear no relationship to the original so it is prioritized, as shown by the ORDER clause in the query.

Later on, I build a second query to get only the first result from this query. That is I’m interested in the ONLY (!) first and earliest transaction (TC) code on an open order (Sym). This should be a simple query as shown below.

SELECT Registry.Sym, First(Register.TCR) AS TC_First, First(Register.DateOpen) AS DateOpenF, First(Register.DateClose) AS DateCloseF, First(Register.KeyO) AS KeyO
FROM Register
WHERE (((Register.DateClose) Is Null))
GROUP BY Register.Sym;

However, the query fails to work properly. Where there is more than one open order (Sym) it may or may not return the proper TC value. Upon inspection, it always returns the TC in the original sort order of the first query, without regard to the ORDER clause.

That is, it’s as if ORDER clause of the first query where not there at all! I depend on this ORDER clause to organize the data in the necessary order but it seems the ORDER is not seen by the second query.

Does anyone have any idea why I cannot pass the sorted data to the second query as shown in the ORDER clause? Is there someway to force the ORDER through to the second query?

Alternately, if one of you DB wizards has a better suggestion to achieve the same result, please feel free to comment.

THANKS IN ADVANCE!!!!

Best Regards,
Mark

[HiTechCoach]

In this case, I would use a sub query.

See:
Subquery basics