crosstab query is datasource of rpt - need to pull more info?

[fredk]

I am even having a problem coming up with a subject line!

Please bear with me here....

I have a report that is based on a crosstab query. The Column heading is "month" so each month is listed accross the report - and the row headings are: "repsname", "repgoal" and "totalsales"

Under each month column, I had a field called monthly goal and I basically used an unbound form that divided the repgoal by 12 - Easy.

Now, because our goals are really weighed by month (January is usually a higher sales month) I used historical data to weigh the monthly goal - For example, January's goal is now 13% of the entire goal instead of just dividing this by 12. Still no problem. I could just set my unbound monthly goal to =[repgoal] * (the hardwired percent) - However, I want this to be dynamic and depending on the year, use the proper percentages in the monthlygoal table...

I am using a table that holds the monthly goal and the percentage of the monthly goal- This percentage changes every year So I have a year field in the table.

I was thinking that I can just use the year on my form (the user enters a year and then clicks the button to run the report) as a criteria but I am a little stumped about the best way to get the numbers I want from the table based on the year on the form.

I hope this makes sense?

Thank you very much!!!

Fred

 

[Darrylles]

Hi,

You have a year field, so why not prompt the user for the Year value from the query?

In the criteria field for the Year, use:

[Please enter Year {nnnn):]

Sorry if you already know about this, just an idea.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[fredk]

Thanks for responding Darrylle - I do have that already set up - The problem is getting the correct years percentage to the report - The reports recordsource is a crosstab query - I just need to somehow get the 2003 monthly percentages to the report

Thanks!!

Fred