Creating dynamic cross tab report

[fortwrestler]

Hello,

I currently have a cross tab query that I would like to create a report with dynamic date ranges.

Right now, if I generate the report from the crosstab query, it becomes a fixed report for a certain date range which can't be changed without regenerating the report.

Here is my SQL for the crosstab.

TRANSFORM Max([Union Of Stops and Summary].NumOfStops) AS MaxOfNumOfStops
SELECT [Union Of Stops and Summary].Line, [Union Of Stops and Summary].Reason
FROM [Union Of Stops and Summary]
GROUP BY [Union Of Stops and Summary].Line, [Union Of Stops and Summary].Reason
PIVOT Format([EntryDate],"Short Date");


Is there a day to make a report from a user inputted date range + 7 days (weekly) and this dynamically changes by the first day entered.

 

[Ranman256]

Instead, make a 'report' table. This table would have every possible column.
A macro of append queries would add 1 column at a time. Errors would be ignored.
Then you'd sum the table to report on it.

 

[GinaWhipp]

You could add a PARAMETER to your query, have a look at...
http://www.access-diva.com/q1.html (should get you started). You could then have a small Form open to allow your Users to select the *start* date.

 

[fortwrestler]

I added the Parameter to my query, however when I go to make the report, I can only get it to pull from the date range given. Is there a way on the report to make it day(1), day(2), etc? so that it auto pulls from the dynamic crosstab

 

[GinaWhipp]

Not sure what you mean... Just create a Form for one to enter the date, set the parameter in the crosstab and then a button to generate the report.

 

[fortwrestler]

Would I have to create a report through VBA for this to work? I think I'm understanding what you are suggesting, I'm just not seeing how to generate a report that's not predefined.

 

[GinaWhipp]

A little confused... Which post are you answering?

 

[fortwrestler]

Yours, Gina....

I understand the parameter part of the crosstab query,
I'm having an issue getting it to display on a report.

When I go to generate the report, the dates/fields are fixed rather than updated according to the parameterized crosstab query.

Is this where I want to use Column Headings?

 

[GinaWhipp]

Did you create the report? Are the Columns headings always the same?

 

[fortwrestler]

I did...Kinda working on it while checking these forums...

So,
I converted the format of the dates to "ddd" (Mon,Tues,etc), and matched up the column headings to this. This translates to the report nicely. I have another field with "Between [start] and [stop]" and this seems to be working....

The only forseeable issue with this is that it is limited to 7 columns (one for each day) and I think this is caused by the column headers....

Is there another way to go about it besides using the predefined column headers?

 

[GinaWhipp]

You mean dynamic column headings?

 

[fortwrestler]

Yes, that would be the more appropriate term.