Add a summary row to MS Access query

Question

I have a query stored in MS Access which is doing a standard select from an Access table. I would like to add a summary row at the end showing sums for some of the data above.

I have looked at DSum() but it isn't suitable as I would have to include the running total on each row as opposed to just the end.

Also, note that I don't want to sum data in column a - I would like to get an empty field for the summary of column a.

Example:

a | b | c
-------------
0 | 1 | 2
1 | 1 | 9

  | 2 | 11 <-- Sums data above

Does anyone know how this problem can be solved in Access? An alternative might be to define a second query which does the aggregation and then merge it with the recordset of the first one, but this doesn't seem particularly elegant to me.

In SQL server it is apparently possible to use "COMPUTE" or "ROLLUP" but these are not supported under MS Access.

Thanks for your constructive criticism David. Can I just point out that I am having to construct an HTML table on the fly with this summary row and then send per e-mail. If you think that this can be solved more easily with a report including HTML formatting etc, then I would love to hear about it. — Patrick
– Patrick, Commented Dec 4, 2009 at 11:05
I have used just such constructions for building HTML myself, it is convenient. — Fionnuala
– Fionnuala, Commented Dec 4, 2009 at 13:18
Access is not always used for finished systems, it is a very useful tool for processing data. — Fionnuala
– Fionnuala, Commented Dec 4, 2009 at 13:32
If you want the full context of your question to be considered, then YOU HAVE TO PROVIDE THE FULL CONTEXT. If I could vote -1 again, I would do so for WHINING. — David-W-Fenton
– David-W-Fenton, Commented Dec 5, 2009 at 1:04

Fionnuala · Accepted Answer · 2009-12-03 11:34:21Z

5

You can use a union query:

SELECT "" As Sort, a,b,c FROM Table
UNION ALL
SELECT "Total" As Sort, Sum(a) As A, Sum(b) As b, Sum(c) As C FROM Table
ORDER BY Sort

EDIT:

SELECT "" As Sort, a,b,c FROM Table
UNION ALL
SELECT "Total" As Sort, "" As A, Sum(b) As b, Sum(c) As C FROM Table
ORDER BY Sort

edited Dec 3, 2009 at 11:34

answered Dec 3, 2009 at 11:06

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Fionnuala Over a year ago

I have made a small change, instead of 1 as Sort, I have used "" and "Total", I think this is better.

Patrick Over a year ago

To add a bit of difficulty to this, I don't want to sum up all the columns just b and c for example.

onedaywhen Over a year ago

-1 "David W. Fenton" is correct: this is something for a report and not suited to a table. If the Access UI didn't obscure VIEWs the error would be more obvious.

Fionnuala Over a year ago

@onedaywhen so how do you build HTML using a report, as per @Patrick's comment? Furthermore Access is often used as a convenient means of processing data, reports do not translate well to other applications.

onedaywhen Over a year ago

@Remou: "how do you build HTML" -- not using Access Database Engine SQL, that's for sure! Save the query (lower case) as a persisted database object and it becomes a Query (Access UI) a.k.a. (VIEW = viewed table). Sorry for mistaking you as one of those sloppy writers who use 'query' when they mean 'Query'... 'cos I just remembered you're the one who doesn't use Query objects or VIEWs, you prefer to persist SQL code in a table lol!

|

Collectives™ on Stack Overflow

Add a summary row to MS Access query

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related