如何使用ms访问查询按月填充结果 [英] 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 

现在，我想显示单行作为具有选定LOB的输出，它必须有12列作为输出，每个月(今年).现在，我使用的查询时间太长，这增加了整体时间.任何人都可以提出解决此类问题的更好方法.

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.

推荐答案

我认为您正在寻找:

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)

---

要回答您的评论，如果需要2年，请尝试以下方法:

---

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'
      )

这篇关于如何使用ms访问查询按月填充结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！