Changing dates in crosstab query

[lowfyr]

Hello. I want to have a report that shows something like:

LocationName
Goal 5/3 5/10 5/17 5/24 5/31

SalesPerson1 10 10 24 13 21 42
SalesPerson2 10 14 4 10 1 17

I need the dates to change by what the user enters when they run the query. I tried a cross tab, but the dates are fixed column headings and I cant get them to change when a new value is entered in the date range.

Any ideas?
Thanks

 

[Darrylles]

Hi,

I hate to do this, because your thread now has 1 response and is 'more unlikely' to get another response, however - I've had probs with cross-tabs recently and messsed with them for days.

I believe, that although the query creates columns dependant on results from a query - you must draw 'all' possible fields on the report. This means that you would have to draw 365 columns on the report for 1 year alone - because the results could feasibly return any day of the year.
If the possible results come from 2 different years - you'd have to draw 730 fields, etc, etc.

Can't see a way around this except by extensive VBA coding.

Please re-post your question if you don't get another response - I'd hate to feel that I'd stopped you getting a solution (though I'm not hopeful that there is one).

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Steve101]

Lowfyr,

Have a look at a thread I answered a while back. It addresses what you're answering, but be warned that its a bit verbose, and quite technical. Unfurtunately, Access does'nt provide a 'high level' cross tab reporting capability.

The thread is: Thread181-244508

Hope that this helps,
Cheers,
Steve

 

[MichaelRed]

from a brief glance, the report is weekly, based on each Friday within the date range, so the process wold APPEAR to already have the mechanisim to aggregate the values by hte week using these dates. IF this is reasonable close, you should be able to have the start & end DATES placed in the query as PARAMETERS. The report should then report on the weeks by the Friday date. You CAN get into trouble if the number of weeks generate more 'weeks' than can reasonable fit on the page layout.

It will take some amount of code and head scratching to organize the proces, but I can assure you that it is possible. I have created this arrangement for a report using months, gnerating a full years worth of date columns where the most recent month is always in the rightmost column and preceeding months trail off to the left - with the month name as the column header. It was a pain, but it can be done.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over