Microsoft Access is a useful tool for quickly analyzing a data set, however, that data may not be structured in a convenient manner for producing a summarized view. As an example, let’s assume that a table exists with the columns “Item,” “Sale Date,” and “Price.”
| Item | Sale Date | Price |
| Widget 1 | 2/1/2012 | $10.00 |
| Widget 2 | 3/1/2012 | $15.00 |
In our summary view, we want to see the data on an annual basis with each of the 12 months as individual columns as opposed to rows. Given the example table, we’ll need to pivot the data on the “Sale Date” column.
In Design View, twelve columns should be created to correspond to each of the 12 months in a year using the following expression. Only January through March are displayed for example purposes.
Jan: Sum(IIf(Month([Sale Date])=1,[Price],0))
Feb: Sum(IIf(Month([Sale Date])=2,[Price],0))
Mar: Sum(IIf(Month([Sale Date])=3,[Price],0))Using these expressions, the result set from the query is structured in the following format:
| Item | Jan | Feb | Mar |
|---|---|---|---|
| Widget 1 | $0.00 | $10.00 | $0.00 |
| Widget 2 | $0.00 | $0.00 | $15.00 |