Crosstab query with dynamic headings

[miketonny]

I have a table i need to run reports on, so i need to run a cross tab query against it. I get to this far:

TRANSFORM Sum(dbo_PSALES.PeriodSales) AS SumOfPeriodSales
SELECT dbo_PSALES.UniqueID
FROM dbo_PSALES
GROUP BY dbo_PSALES.UniqueID
PIVOT dbo_PSALES.PeriodNo In (26,25,24,23,22,21);

Where the query runs fine, but as you can see i hardcoded the period numbers into the query. How do i turn it into a dynamic one? so everytime new period adds into the database query result will change also?

 

[apr pillai]

Remove the: In (26,25,24,23,22,21) part from the SQL or from the Column Headings values from the Query Property Sheet.

 

[miketonny]

thx, but that doesn't solve the problem, i have 26 periods from 1 - 26,the reason i coded these in are i only wanted it to show the latest 6 periods.
so next months a new period 27 comes in, i have to code 27 in the query, is there a better way of doing this?

 

[cimathers]

TRANSFORM Sum(dbo_PSALES.PeriodSales) AS SumOfPeriodSales
SELECT dbo_PSALES.UniqueID
FROM dbo_PSALES
GROUP BY dbo_PSALES.UniqueID
PIVOT dbo_PSALES.PeriodNo In (26,25,24,23,22,21);

You could try:

TRANSFORM Sum(dbo_PSALES.PeriodSales) AS SumOfPeriodSales
SELECT dbo_PSALES.UniqueID
FROM dbo_PSALES
GROUP BY dbo_PSALES.UniqueID
PIVOT BOTTOM (6) dbo_PSALES.PeriodNo;

I dont know if this will work or not. Please let me know if it does.

Chris.