Aggregate (Group By) function

[Marty]

This has been a vexing problem for me for the longest
time. How do I create a query that has the group by
function on one field but not all the others? My solution
in the past was to do a make table query of the group then
link that table with another query to add the rest of the
fields. This has got to be the dumbest way to do it but I
know of no other way. I have finally had it; there's got
to be a better way.

Please help, Marty

 

[Allen Browne]

The stacked query you describe is sometimes the best way.

You may be able to use First rather than Group By. (First just grabs the
first match available, instead of grouping on the field.)

If you just need one field from another table, a subquery may be useful.
There is an explanation of this option and others in article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm