column by date 1

[rlp]

My table includes "Amount" and "Month" fields (1,2,3, etc). I want the amounts to appear in columns by month. Have tried to create columns for Amount with criteria of Month = 1, Month = 2, but it doesn't work. Have tried (SELECT) statements but can't seem to put together correct syntax. Suggestions on how to build this Query? Thanks. RLP

 

[HowieG]

Try:

SELECT Amount, 
   Month, 
   iif(Month=1,Amount,0) AS Month01, 
   iif(Month=2,Amount,0) AS Month02, 
   iif(Month=3,Amount,0) AS Month03, 
   iif(Month=4,Amount,0) AS Month04, 
   iif(Month=5,Amount,0) AS Month05, 
   iif(Month=6,Amount,0) AS Month06, 
   iif(Month=7,Amount,0) AS Month07, 
   iif(Month=8,Amount,0) AS Month08, 
   iif(Month=9,Amount,0) AS Month09, 
   iif(Month=10,Amount,0) AS Month10, 
   iif(Month=11,Amount,0) AS Month11, 
   iif(Month=12,Amount,0) AS Month12 
FROM YourTableName;

 

[HowieG]

I don't know what other fields are in your table, but if you want to subtotal by month on something like a Transaction Type, then modify my earlier query by adding a GROUP BY clause (easiest to do this in the query design environment).

 

[rlp]

Thanks, I will give this a try. I'm a novice so will ask for some clarification: Month01, Month02, etc are field names for the monthly columns? Your suggested criteria would go into each of those columns? Why does the "if" statement use "iif"?

 

[HowieG]

Yes, the column names would be Month01 etc. If you want to do this in the design environment, make the Field say:

Month01: iif(Month=1,Amount,0)

and similar for the rest.

To use if logic in a function like this, you use iif(test,true,false), but within VB code, you use the IF THEN ELSE ENDIF format. Same general idea, but different uses.