[AccessD] More in Queries and Memo Fields

Gustav Brock gustav at cactus.dk
Tue Jul 12 15:19:53 CDT 2011


Hi Dan

But First just returns the value from "one of the first rows", thus it isn't of much use. Actually, I have _never_ found a situation where First was of any use.

/gustav


>>> df.waters at comcast.net 12-07-2011 21:39 >>>
This is the complete paragraph:

------------------
FIRST versus GROUP BY

SELECT EmployeeID, LastName, Notes
FROM Employees
GROUP BY EmployeeID, LastName, Notes;
SELECT EmployeeID, First(LastName) AS FirstOfLastName, 
First(Notes) AS FirstOfNotes
FROM Employees
GROUP BY EmployeeID;

When you add a field to a Totals query, Access offers Group By in the Total
row. The default behavior, therefore, is that Access must group on all these
fields.

A primary key is unique. So, if you group by the primary key field, there is
no need to group by other fields in that table. You can optimize the query
by choosing First instead of Group By in the Total row under the other
fields. First allows JET to return the value from the first matching record,
without needing to group by the field.

This makes a major difference with Memo fields. If you GROUP BY a memo
(Notes in the example), Access compares only the first 255 characters, and
the rest are truncated! By choosing First instead of Group By, JET is free
to return the entire memo field from the first match. So not only is it more
efficient; it actually solves the the problem of memo fields being chopped
off.

(A downside of using First is that the fields are aliased, e.g.
FirstOfNotes.)
------------------






More information about the AccessD mailing list