Summary Queries

[TP1959]

I'm trying to run a simple summary select query to effectively filter out
multiple occurrences of database records by Grouping them via the "First"
criteria. For some reason, that I am unable to fathom, when I do this the
main description field that I want to display is truncated.

Does anybody know what I'm doing wrong and is there a way around this?

 

[Jeff Boyce]

What Access means by "First" and what you mean by "First" is probably NOT
the same.

If you are using a date/time field for this selection/aggregation, do you
mean the earliest date or the most recent date (MIN or MAX)?

If you are using a sequential number for this, again, the smallest (MIN) or
the largest (MAX)?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dale Fye]

Any time you use a Group By or Distinct clause in a query containing a memo
field, the field will get truncated. How you work around this will depend on
your table structure and the query.

BTW, I recommend against using the "First" criteria, as you mentioned.
Unless you know the records will be returned in a specific order.

Why don't you post both and identify which field is the memo field that is
being truncated. I'll get back to you with a recommended modification.

 

[KARL DEWEY]

If the main description field is a memo field then use another field for the
Max like this --
SELECT [main description field], [field1], [field2]
FROM [TableX]
WHERE Left([main description field], 255) = (SELECT Max(Left([XX].[main
description field]), 255) FROM [TableX] AS [XX]);