CODEWITHSUNDEEP
sqlms-access
Nikhil Srivastava
Nikhil Srivastava

Reputation: 239

How to get result populated month wise using ms access query

I have a table which is having following structure:

    PID
    Year
    Month 
    LOB
    Contracts

Now i want to display a single row as output with selected LOB and it must have 12 column as output, each for each month(this year). Right now i am using a query which is too long which is increasing the overall time. Can anyone suggest better way to approach this kind of problem.

Upvotes: 0

Views: 180

Answers (1)

Thomas G
Thomas G

Reputation: 10226

I think you are looking for this:

TRANSFORM COUNT([Contracts])
SELECT [LOB]
FROM [Table]
WHERE [Year]=2016 
  AND [LOB]='xxx'
GROUP BY [LOB]
PIVOT [Month] IN (1,2,3,4,5,6,7,8,9,10,11,12)

To answer your comment, if you want 2 years, try this :

TRANSFORM COUNT([Contracts])
SELECT [LOB]
FROM [Table]
WHERE [Year] IN (2015,2016) 
  AND [LOB]='xxx'
GROUP BY [LOB]
PIVOT ([Year] & '-' & [Month]) IN
      (
       '2015-1','2015-2','2015-3','2015-4','2015-5','2015-6','2015-7','2015-8','2015-9','2015-10','2015-11','2015-12',
       '2016-1','2016-2','2016-3','2016-4','2016-5','2016-6','2016-7','2016-8','2016-9','2016-10','2016-11','2016-12'
      )

Upvotes: 3

Related Questions