[AccessD] More in Queries and Memo Fields

Gustav,
I think it can be useful. If the fields you are about are being forced to
duplicate in the query because of some other field which you don't need to
see in all its variations. If you're comfortable taking a representative
example of that field, you can use First to show one of its members. FIRST
and LAST offer us something that we can do in the Designer rather than
resorting to the SQL window. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, July 12, 2011 4: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