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 Fye wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:319993B8-04DE-4F5F-BFC2-(E-Mail Removed)...
>> 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" wrote:
>>
>>> 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
>
>
|
Similar Threads
