[AccessD] More in Queries and Memo Fields

Well - I haven't tried what Allen was suggesting - just thought it was
something to add to the discussion.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, July 12, 2011 3:20 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] More in Queries and Memo Fields

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.)
------------------



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com