optimizing ORDER BY query for Access

[Oscar]

This is the actual query :

recordset rstA:
"SELECT cancelled FROM tblOrders WHERE ID_company=" & ID_comp & " ORDER BY
ID DESC"

IF NOT rstA.eof THEN
IF rstA!cancelled=FALSE then
(process something)
ENDIF
ENDIF

This works fine, however I want to optimize the query by adding the
verification of column cancelled within the original query so that I only
need to verify if the recordset holds a record. I want to know how I can
build the query which first finds the latest added order of a company and
verifies if the order has been cancelled by verifying the value of column
'cancelled'. In case I use WHERE cancelled=0 :

"SELECT cancelled FROM tblOrders WHERE ID_company=" & ID_comp & " AND
cancelled=0 ORDER BY ID DESC"

it selects the records with column cancelled set to false first which
doesn't need to be the latest within the table.
So my question is : how can I implement this verification into one query
such that it results only one record when the latest order has not been
cancelled and no rows otherwise?

 

[Jason Lepack]

Don't multi-thread.

If you only care about the most recent ID then only return that
record.

Cheers,
Jason Lepack

 

[John Spencer]

"SELECT Cancelled FROM TblOrder " & _
" WHERE Id_Company = " & Id_Comp & " AND Cancelled = False " & _
" AND ID = (SELECT Max(ID) FROM tblOrders WHERE Id_Company = " & Id_Comp &
")"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Oscar]

Hi John,

this is exactly what I was looking for. Your suggestion works fine for my
case. Thanks a lot for that.

The query actually is a part of a large query and is implemented as :

& " IIF(EXISTS(SELECT cancelled FROM tblOrders WHERE
ID_comp=tblArbCtr.compID AND cancelled=FALSE AND ID = (SELECT Max(ID) FROM
tblOrdes WHERE ID_comp = tblArbCtr.compID)), 1, 0) AS OrderCancelled ," _

There is only one drawback : without this addition the total query took me
about 1 second. Now it takes about 12 seconds. Any idea why this part is so
time-consuming, any alternatives?

regards,
Oscar

 

[John Spencer]

Well, it takes a while since you are running the query once for each record
in the main query.

Indexes will help.

Beyond that you could try embedding a subquery in the FROM clause and using
that as if it were a table.

Something like the following would get the LAST id for each ID_Comp.


SELECT ID_Comp, Max(ID) as LastID
FROM tblOrders
GROUP BY Id_Comp

Then in a simple query that gets just the most recent tblOrders record for
each ID_Comp

SELECT TblOrders.*
FROM tblOrders INNER JOIN
(SELECT ID_Comp, Max(ID) as LastID
FROM tblOrders
GROUP BY Id_Comp) as x
ON TblOrders.Id_Comp = x.ID_Comp and
TblOrder.ID = x.LastID
WHERE tblOrders.Cancelled = True


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..