Query to do a cross-tab (I think!)

[Allilue]

Hello, Can someone help with setting up a query please? I have a table that Has a Start Month and various periods (months) to get to an End Date. I've only specified a column for Start Month, Year, and Number of Months. Is there a query I can do that will help to show all months in columns with amounts that stop after the number of periods is up? Ideally, it should look like

Jan 2005 Feb 2005 ..... Oct 2007 Nov 2007?
$500 $500 $500

Hope this makes sense!

 

[dhookom]

Where did the $500 come from? You didn't provide a field for this or a table name. Also, we don't know what type of values you are storing in your fields. Assuming fields"
[tt]
Name Data Type
StartMonth Numeric (month number 1-12)
Year Numeric (year: 2005, 2006,...)
NumberOfMonths Numeric
CurrencyField Currency
[/tt]
Consider this solution. Create a table of numbers (tblNums) with a single numeric field (Num) and values 0 through your maximum Number of Months. You can then create a crosstab query with sql like:

TRANSFORM Sum(CurrencyField) AS SumOfCurrencyField
SELECT StartMonth, Year, NumberOfMonths
FROM tblNoNameGiven, tblNums
WHERE Num<=[NumberOfMonths]
GROUP BY StartMonth, Year, NumberOfMonths
ORDER BY DateSerial([Year],[StartMonth]+[Num],1)
PIVOT DateSerial([Year],[StartMonth]+[Num],1);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]