CODEWITHSUNDEEP
sqlms-accesssum
rob
rob

Reputation: 24

Producing a Sum Query

I am using MS Access 2007. I'm trying to produce a query using the following data.

SP      Location     Manager
100     On-site      Jones
200     On-site      Driver
200     Off-site     Driver
300     On-site      Jones
150     Off-site     Jones
200     On-site      Jones
100     Off-site     Driver

I want to sum the SP for each manager at each location. The desired output should be:

Jones is On-site    Jones is Off-site    Driver is On-site    Driver is Off-site
      600                 150                  200                   300

How do I do this?

I can produce a query for just 'Jones is On-site' but as soon as I add the others (ie 'Jones is off site', etc) it doesn't work.

This is the code I used for 'Jones is On-site'

SELECT Sum([Selling Price]) AS Expr1
FROM [Table]
WHERE ((([Table].[Location])='Off-Site') AND (([Table].[Manager])='Jones'));

Upvotes: 0

Views: 60

Answers (2)

Ruslan Veselov
Ruslan Veselov

Reputation: 337

If you want to make query like in your desired output try this:

SELECT 
    [Jones is On-site], 
    [Jones is Off-site], 
    [Driver is On-site], 
    [Driver is Off-site]
FROM
(
    SELECT SUM(SP) AS SP, Manager + ' is ' + Location as Col1
    FROM Test
    GROUP BY Manager + ' is ' + Location
) XXX
PIVOT
(
   SUM(SP) FOR Col1 IN (
        [Jones is On-site], 
        [Jones is Off-site], 
        [Driver is On-site], 
        [Driver is Off-site]
   )
) AS ResultTable

See demo here.

Upvotes: 0

Bulat
Bulat

Reputation: 6979

This should work:

SELECT SUM(SP), Manager & " is " & Locaton
FROM table
GROUP BY Manager & " is " & Locaton

Upvotes: 2

Related Questions