Conditional Column Names

[LadyDi]

I am trying to create a rolling twelve month query that will total the months across. I set the query up so that it is a rolling twelve month, but I am having difficulty getting it to list the months across. I would like it to take the first month, list it as a column and show the totals according and the same with each month after. Here is the query that I currently have:

SELECT WIPHistoryTbl.WIP_PART_NUM, WIPHistoryTbl.WIP_MONTH, WIPHistoryTbl.WIP_YEAR, SpinsMonthEndTbl.WEEKS_IN_MONTH, WIPHistoryTbl.WIP_AMOUNT, IIf(Count(Switch([WIP_MONTH_NUM]='06',1))=1,Sum([WIP_AMOUNT]),' ') AS JUNE_TOTALS
FROM SpinsMonthEndTbl INNER JOIN WIPHistoryTbl ON (SpinsMonthEndTbl.SPINS_YEAR = WIPHistoryTbl.WIP_YEAR) AND (SpinsMonthEndTbl.ACCT_MONTH = WIPHistoryTbl.WIP_MONTH_NUM)
GROUP BY WIPHistoryTbl.WIP_PART_NUM, WIPHistoryTbl.WIP_MONTH, WIPHistoryTbl.WIP_YEAR, SpinsMonthEndTbl.WEEKS_IN_MONTH, WIPHistoryTbl.WIP_AMOUNT, WIPHistoryTbl.WIP_MONTH_NUM, SpinsMonthEndTbl.BEG_DATE, SpinsMonthEndTbl.END_DATE
HAVING (((SpinsMonthEndTbl.BEG_DATE) Between (Now()-365) And (Now())))
ORDER BY WIPHistoryTbl.WIP_YEAR, WIPHistoryTbl.WIP_MONTH_NUM;

I would really like it if the query could look at the BEG_DATE, select the corresponding ACCT_MONTH and show it as the column header. Is that possible?

These are what I would like my results to look like:
PART_NUM | 06_TOTALS | 07_TOTALS | 08_TOTALS . . . . . . . . .
123456789 | 10 | 5 | 15 .............................

Can I do that? Any assistance you can provide would be greatly appreciated.

 

[LadyDi]

I have modified my tables as suggested elsewhere on the forum. My tables now show ORDER_PART_NUM, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The problem I am having now is getting it to show totals for each part by month horizontally.

 

[JHB]

Use a cross tab query.
If you can't get it post some sample data in a database.