Need help on dynamic report

[SG]

Hi,

I'm assigned a task to generate a dynamic report which shows the sales
number based on the month chosen.
The structure of the table 'Sales' is
account, name, $Jan-lastyr, $feb-lastyr, $mar-lastyr....., $Jan-thisyr,
$Feb-thisyr, $Mar-thisyr.....
I have a form "Form1" for user to be able to choose the month they are
looking at. If user chose month 'Jan' (always the month of current year), on
the report, I like my report to show from Jan Last year forward 12 month
sales which is up till this Jan by month.
For example:
acccount, name, Janly, febly,........Jan-thisyear
If user chose "March", the report will be like:
account, name, Marly, Aprly.......Mar-thisyear.

I have no clue how to make this report with heading and data changing
whenever the month changed.
I need a help on this.

thanks in advance,

Sarah

 

[SG]

Sorry, I posted to the wrong group.
Sarah

 

[Klatuu]

No problem. I did just such a report. On the report side, in the On Open
event of the report, set the captions for your header labels. Don't Use
specific months, but names that will be easy to use. for example, I had to
have 8 columns and depending on the month, show some months and some quarters
in the columns, so I named my text boxes like txtCol1Plan, txtCol1Actual,
txtCol1Variance, txtCol2Plan, etc.
For my column headers it was lblCol1, lbl Col2, etc
Then if you have to do by month, you can reference the form you use to open
the report to set up your labels. There are several coding techniques to
convert a month number to text, but too much detail for now.
Now, the data is the tricky part. My solution was to create a temp table
and use VBA to read in the data from the data tables and create rows with the
data in the correct columns for the temp table which is the data source for
my report. It may be possible to create a query smart enough to position the
data, but I have not really looked at that possibility.