Subtotal Row in Query

  • Thread starter Thread starter matt
  • Start date Start date
M

matt

I have created a Top 25 report in a query that I export to
Excel for our management team to review. They have
requested that I include a total dollar amount of the Top
25.

How do I get this to show up in a query instead of
creating a form or report?

Thanks..Matt
 
You can probably do it with a Union query.

Select Top 25 SomeField
From SomeTable
UNION
Select Sum(SomeField)
From SomeTable
where SomeField IN (Select Top 25 SomeField
From SomeTable);
 
Dear Matt:

It is generally easier to create a report and let it do that. I'll
provide a query alternative as you asked.

You should write a query for the values you want (probably what you
already have). Write a separate query for the totals. You can
combine them using UNION ALL into one query. They must both have the
same number of columns and column types.

The problem now is to be able to order this so the totals come at the
end of the groups they total. In each of the 2 select queries, add a
column I usually call SEQ. In the detailed query let this be the
value 0, in the totals query make it 1. Sorting on the common "group"
columns that are being totalled, you can add this SEQ column to make
the totals come out last.

For my money, I'd create the report. There's a lot you can do in the
report that's going to be real work in the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top