Access DSUM in Query

Question

I have a table that identifies costs by Location and Department. I would like to build/run a query that identifies information by the Location and Department (as shown below).

Building Electric Department Plumbing Department Roofing Department Etc.
A $ $ $ $
B $ $ $ $
C $ $ $ $
D $ $ $ $
Etc. $ $ $ $

I assume that DSUM is the best method. Can anyone assist?

Thanks

Answer 1

Have you tried using a Totals query?

Answer 2

The normal solution would be a crosstab query, e.g.

TRANSFORM SUM(Costs)
SELECT Location
FROM TableNameGoesHere
GROUP BY Location
PIVOT Department IN("Electric","Plumbing","Roofing", etc);

Note that the values in the IN clause must be exactly those values which appear in the Department column in the table.