asked on

query help

I have the following access table

Shop Month Sales
1000 1 1000
1000 2 2000
2000 1 4000
2000 2 5000

Basically there is a list of shops with sales for different months

I am trying to write a query that will list the shops and the sales
for each month across a single line
(all shops will have the same number of sales month entries)

For the sample data above I was hoping for the following

shop month1 month2
1000 1000 2000
2000 4000 5000

Guy Hengel [angelIII / a3]

this will do, you can add more values for "month" as needed.

select shop
  , sum(case when month=1 then Sales end) month1
  , sum(case when month=2 then Sales end) month2
 from yourtable
 group by shop

Open in new window

Rey Obrero (Capricorn1)

in access

select shop
, sum(iif(month=1,[Month])) as Month1
, sum(iif(month=2,[Month])) as Month2
from table
group by shop

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

johnnyg123

ASKER

my table name is table1 so I tried

select shop , sum(case when month=1 then Sales end) , sum(case when month=2 then Sales end)
from table1
group by shop

got syntax error (missing operator) in query expression ' sum(case when month=1 then sales end)'

Rey Obrero (Capricorn1)

johnnyg123,

are you doing this in Access or SQLServer?

Guy Hengel [angelIII / a3]

ms access:

select shop  , sum(iif(month=1,Sales,0)  , sum(iif(month=2,Sales,0)
 from table1
 group by shop

Open in new window

GRayL

For Access use the Cross tab query shown to you by Capricorn1.