Show column in totals query without grouping

[Guest]

I get stuck on this every time. . . I have a totals query and I need to
include a column to sort by that is not shown in the query results. How do I
add that column without having to group by, which messes up the query? I
have Last in the Total row of the design grid, which seems to be working, but
I hesitate to use that since I don't know what Last means and why it's
working. How can I include a column in a total query without having to group
by it? Thank you.

 

[Michel Walsh]

You have to either include the column in the GROUP BY, either to aggregate
it (through SUM, COUNT, MIN, MAX, FIRST, LAST, .... ), when you use a column
name in a total query. If you aren't sure about LAST, try MIN, or MAX, which
are more deterministic:


SELECT f1, f2, MAX(f3)
FROM tableName
GROUP BY f1, f2
ORDER BY f1, MAX(f3)



as example.

The reason for the enforced either group, either aggregate, is because with
data like:


f1 f2 f3 ' fields
10 10 aaa
10 10 bbb ' data



what will be the result of:

SELECT f1, f2, f3
FROM tableName
GROUP BY f1, f2


mainly, what will be supplied for f3? So, if you include f3 in the GROUP BY,
or if you aggregate it (here, with MIN, MAX, FIRST or LAST), that would
remove, somehow, the indetermination.




Hoping it may help,
Vanderghast, Access MVP