Dale Fye's method is a good one if you need to be able to edit the records in
the query or if you have NOT followed the naming guidelines for tables and
fields (Letters, Numbers, and underscore characters only and not RESERVED
words such as Date).
Your original method is generally faster but I believe you will find that you
cannot edit the records returned by this query.
One additional option is to use nested queries if your field and table names
do not follow the naming guidelines.
Save a query as q1
SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
FROM tbl_qt
GROUP BY tbl_qt.unit_no
Now use that query in your subsequent query
SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
FROM Q1 AS Temp1
LEFT JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
(Temp1.cngu_no = tbl_qt.unit_no);
In your existing query you might drop the order by clause in the subquery as
Dale noted. Although it will probably be ignored since it is not needed for
the execution of the subquery.
And I would change the LEFT JOIN to an INNER JOIN. I don't KNOW that an INNER
JOIN is more efficient, but I SUSPECT that it might be and I can see no reason
not to use an INNER JOIN since there has to be a matching record.
SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
FROM [
SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
FROM tbl_qt GROUP BY tbl_qt.unit_no
]. AS Temp1
INNER JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
(Temp1.cngu_no = tbl_qt.unit_no)
QB
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Dale said:
QB,
If what you are trying to do is get the unit #, and the 1 city associated
with the Maximum (ETA_Date) for each unit, then your tecnique below would
work (although I would move the Order By clause out of the subquery and put
it in the outer part of the query. Another method to do this is:
SELECT Unit_No, Eta_Date, Eta_City
FROM tbl_qt
WHERE eta_Date = (SELECT Max(Eta_Date)
FROM tbl_qt as T
WHERE T.Unit_No = tbl_Qt.Unit_No)
Personally, I prefer the method you used, but its always good to know your
alternatives.
HTH
Dale
QB said:
I came up with the following. It seems to work, but would still like to
know
if a) this is proper, b) if there is a more efficient method
SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
FROM [
SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
FROM tbl_qt GROUP BY tbl_qt.unit_no ORDER BY tbl_qt.unit_no
]. AS Temp1
LEFT JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
(Temp1.cngu_no = tbl_qt.unit_no);
QB
QB said:
I am obviously not understanding something very basic with regard to
Group BY
and Max() sql statements.
All I want is to return the entry which has the greatest date value per
unit
no. I have the following
SELECT unit_no, Max(eta_date) AS MaxOfeta_date, tbl_cngu_qt.eta_city
FROM tbl_qt
GROUP BY unit_no, eta_city
ORDER BY unit_no;
but it returns multiple entries per unit??
What is the proper synthax for this query?
QB
