Can I use totals on one field and still have a memo field?

[KevinS]

I have a query that has worked for years but now I have been asked to add a
memo field to it.

When I redesign and run the query it tells me that "You tried to execute a
query that doesn't include the specific expression <Field name> as part of an
aggregate."

The <field name> is the field that contains the memo field. If I tell it
not to display the memo field the query works fine.

I tried to delete the group by for that field but that didn't work. If I
eliminate the field that is being grouped I loose the uniqueness of the name
field.

Any help appreciated!

 

[John Spencer MVP]

If you don't care which of the associated memo fields you get then you can use
FIRST or LAST and have the one of the memo field's values returned for the group.

If you need every memo field returned, post back with some more details on
your table structure.

One TRICK you could use would be to truncate the memo field to 255 characters.
and then use something like this which will return the different memo field
values if the values vary in the first 255 characters.

SELECT SomeField, Sum(Dollars), First(Memo) as TheMemo
FROM SomeTable
GROUP BY SomeField, Left(Memo,255)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

Not in the same TOTAL (GROUP BY) query.

You can try to make ANOTHER query where you will JOIN the total query
(without the memo) on one side of the join and with the initial table on the
other side of the join, to preserve your memo field.



Vanderghast, Access MVP

 

[KARL DEWEY]

Each record would have a memo field in that table. If you try to total
records, roll them up, you can not have the memo field as the memo field will
not total.
You can total a field with some field for grouping then you could join the
total query to the orignal table.
The data would look like this --
A Sets 5
A Cans 3
A Box 4
A Bags 2
A Box 3
B Bag 1
B Box 3
Totals --
A 17
B 4
Joined --
A Sets 17
A Cans 17
A Box 17
A Bags 17
A Box 17
B Bag 4
B Box 4

 

[KevinS]

Thank you. I thought of that but my user wants to see everything. H has
agreed to a smaller field size so I changed it to a text field and that seems
to be working.
Thank you,
KevinS