MS ACCESS database broken into month

Question

Back again with an issue that has been bugging me for sometime now. Here's my dilemma. I have a table looks like this

| ID |   DATE         |  SALES |
  1     2012-01-20      100.45
  1     2012-01-22       55.45
  1     2012-02-15       66.77
  3     2012-01-19       33.33  
  3     2012-02-19       69.44
  4     2012-02-22       77.55
  etc

So what i need to do is make a new table with all the ID grouped in one, and then adding their sales in the month range (i.e 2012-01-01 to 2012-01-30)

The new table would looke like this

ID    |   01-2012    |   02-2012   |  03-2012 | etc | etc
1     |   155.90     |     66.77   |
3     |  33.33       |     69.44   |
4     | 000.00       |    77.55    |

All the recoods need to display even if they sum up to none

any input or help is much appreciated! Thank you internet!

You need to transform data (pivot)! There's no need to export these data into another table. — Maciej Los, Jan 14 '15 at 20:34
Have a look at PIVOT. When you do your PIVOT you will need to manipulate the date so that it only looks at MM/YYYY otherwise you will end up with a column for every date in your data. Have a go, if you get stuck post your cose and we will have a look. — G B, Jan 14 '15 at 20:37

Maciej Los · Accepted Answer · 2015-01-14T21:36:26.040

1

You can pivot data by using Transform command.

TRANSFORM SUM(SALES)
SELECT ID, FORMAT([DATE], 'yyyy-MM')
FROM YourTableName
WHERE [DATE] BETWEEN #Date1# AND #Date2#
GROUP BY ID, FORMAT([DATE], 'yyyy-MM')

Replace Date1 and Date2 with proper dates (in ISO format: MM/dd/yyyy).

By The Way: never use reserved words as a column name!

See: SQL Reserved Words

edited Jan 14 '15 at 21:36

answered Jan 14 '15 at 20:36

Maciej Los

8,468
1
20
35

Can you transform and also Sum by groupped date? – Sao Tith Jan 14 '15 at 20:39
As i mentioned: Yes, you can! – Maciej Los Jan 14 '15 at 20:43
the column nmaes were abritutary for example purposes :) Thank you will test this out – Sao Tith Jan 14 '15 at 20:46
Okay it worked but needed to add the pivot Sales at the end new problem however, i'm now getting an error that says it's making to many columns can i break this out into years there is 5 years of data in this table? – Sao Tith Jan 14 '15 at 20:56
Define date range for example for one month o group data by month, year. – Maciej Los Jan 14 '15 at 20:57
would i through the where after the from table like so ((date Between #1/1/2010# And #1/31/2010#))? if it was for one month period? also i should mention my date data has time – Sao Tith Jan 14 '15 at 21:06
??? i can't get you, sorry... – Maciej Los Jan 14 '15 at 21:08
sorry hit enter to soon, see above note – Sao Tith Jan 14 '15 at 21:09
Yes, you can use above WHERE statement. Please, see updated answer ;) – Maciej Los Jan 14 '15 at 21:10
Okay that worked a bit better, howver it's putting every single column in the header that has a differnt time stamp, how can i prevent the pivot from looking at individual time stamp and just group the actual day together – Sao Tith Jan 14 '15 at 21:19
all the table headings are 2012-01-01 9:25:35am 2012-01-01 10:10:20am we need to group those into the same day – Sao Tith Jan 14 '15 at 21:20
Or better group them into the same month – Sao Tith Jan 14 '15 at 21:23
Feel free to add **MONTH([DATE])** or **FORMAT([DATE],"YYYY/MM/DD")** function ;) – Maciej Los Jan 14 '15 at 21:26
First of all thank you so much for your quick response. really appreciate. I'm fairly new to access and have not used the format function where would this be placed in the SQl – Sao Tith Jan 14 '15 at 21:36
Answer has been updated again ;) – Maciej Los Jan 14 '15 at 21:37
Thanks again worked perfect! Although i have one more task at hand but i'll most likely post a follow up question with you :) – Sao Tith Jan 14 '15 at 21:49
Thank you for recognition. Please, post another question. There's tons of experts ;) Someone will help you. Maybe it would be me ;) – Maciej Los Jan 14 '15 at 21:52

MS ACCESS database broken into month

1 Answers1

Linked