question

RJF-4684 avatar image
0 Votes"
RJF-4684 asked KenSheridan-7466 answered

Access DSUM in Query

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


office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Have you tried using a Totals query?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.