Vertical to Horizontal Table with Month Grouping

1
votes

Long time lurker - first time poster.

I'm new to Access and have a data table I'm trying to manipulate via a query.

Current View:

ID  | $Value | Month   
1   | 184    | 10/1/2012    
1   | 186    | 11/1/2012     
1   | 176    | 12/1/2012     
1   | 183    | 1/1/2013     
1   | 192    | 2/1/2013     
1   | 201    | 3/1/2013     
1   | 183    | 4/1/2013     
1   | 179    | 5/1/2013     
1   | 177    | 6/1/2013     
1   | 135    | 7/1/2013     
1   | 202    | 8/1/2013     
1   | 188    | 9/1/2013     
2   | 258    | 5/1/2013     
2   | 126    | 6/1/2013     
2   | 236    | 7/1/2013     
2   | 367    | 8/1/2013     
2   | 450    | 9/1/2013     
2   | 186    | 10/1/2013     
2   | 248    | 11/1/2013     
2   | 264    | 12/1/2013     
2   | 257    | 1/1/2014     
2   | 264    | 2/1/2014     
2   | 138    | 3/1/2014     
2   | 264    | 4/1/2014

Desired Ending View:

ID | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | ... | Month 12    
1  | 184     | 186     | 176     | 183     | 192     | ... | 188    
2  | 258     | 126     | 236     | 367     | 450     | ... | 264

I'm attempting to transpose the data in Access so that there is only one row per ID and group the months (always labelled 'Month 1', 'Month 2', etc...). I only want to see 12 columns of months (Not one for each month and year). So in the above example ID 1, Month 1 is 184 and ID 2, Month 1 is 258, despite the month and year of each ID being different.

Any guidance/help is greatly appreciated.

3
sqlms-access
I can't see your images, like many of us at the office. So I can't answer. It's better to paste raw text instead. You are probaly looking for a PIVOT, check my recent answer here, it might help And I guess you wrongly tagged your question, it should be sql and not mysql, if so please edit and fix the tag. - Thomas G
ms access has a built in pivot table functionality. Have you tried it? - Shadow
@Thomas G, thanks I've edited as per your suggestion and am looking into your previous answer. - Curtis MacD
@Shadow, I've tried to use it but run into trouble grouping the months - Curtis MacD
Do you expect me to use my mind reading abilities to find out what has gone wrong, or are you going to share it with us, sparing yourself from the indignity of a mind read? - Shadow

3 Answers

1
votes

Considering that you have only one value per ID and month, use FIRST aggregate function and not SUM or MAX as proposed by my fellows.

TRANSFORM FIRST([$Value]) 
SELECT [ID] 
FROM [Current View] 
GROUP BY [ID] 
PIVOT "Mo" & Month([Month])

If you want all years and months (yyyy-mm)

TRANSFORM FIRST([$Value]) 
SELECT [ID] 
FROM [Current View] 
GROUP BY [ID] 
PIVOT (Year([Month]) & '-' & Month([Month]))
1
votes

I'm not too familiar with the graphical query editor interface, but you can definitely do this in SQL using the TRANSFORM and PIVOT features along with the month() function. In fact, in the graphical interface, you may just be missing the month() function. I believe the sql version would look like this:

TRANSFORM Max([Current View].[$Value])
SELECT  [Current View].[ID] 
from [Current View] inner join  
     ( select tbl1.[ID], min(cdate(tbl1.[Month])) as minDate
       from [Current View] as tbl1
       group by tbl1.[ID]) as earliestDate on [Current View].[ID] = earliestDate.[ID]
Where datediff("m", earliestDate.minDate, [Current View].[Month])<=12
group by [Current View].[ID]
PIVOT (datediff("m", earliestDate.minDate, [Current View].[Month]) +1 )
;

EDIT: Added on logic to define months based on the starting month for each ID, and restrict to only the 12 following months.

0
votes

As has been suggested - a crosstab (pivot) query would work here:

TRANSFORM   Sum(sValue) AS SumOfsValue
SELECT      ID
FROM        Table1
GROUP       BY ID
PIVOT       "Mo" & Month([dMonth])

Is your 'Desired Ending View' correct?
e.g. You've got 188 for ID 1 in month 12 - shouldn't this be 176?