Access DSUM in Query

Randal Flinn 311 Reputation points
2021-04-19T19:00:35.253+00:00

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
849 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 2,301 Reputation points
    2021-04-20T01:10:20.637+00:00

    Have you tried using a Totals query?

    0 comments
  2. Ken Sheridan 2,756 Reputation points
    2021-04-21T17:53:24.77+00:00

    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.

    0 comments