Link to home
Start Free TrialLog in
Avatar of JSneil
JSneil

asked on

Dynamic Crosstab Report In Access

i have produced a cross tab query as follows :


The row contain Item and Serial Nr
The columns contain each day within a date range (determined through user criteria).

For each day, i want to show the hours worked.

I want to produce this report dynamically as I will never know how many columns (dates) are going to be in the report.

Whats the best way to do this.. I've had a bit of a read online but it's a bit of a mish mash.

I've never created a report on the fly before and whilst having a play just, i couldnt even change the page orientation in code...

Thanks

I've included a pic which gives you an idea of some of the raw data
raw.PNG
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The kicker with doing this is that a crosstab query "Dynamically" builds the fields (Columns)
However, a Report will "Hardcode" the fields...

I have done similar things that involved Aggregate functions, but again, it is always a nightmare.
Perhaps another expert has had a more positive experience......


Another simple workarond is to simply insert the crosstab query into a blank report.


JeffCoachman
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jeff, thanks for referencing my paper.

JSnell: Did you get a chance to read my paper and look at the sample database for Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query?

That example automatically adjusts the report column headings for the month no matter what time period is selected.
Here is a sample of just inserting the crosstab query in a report and a sample database.

Not as "pretty" as the more complex techniques (and may have it's own drawbacks depending on you ultimate need)
...but not special code or design work is needed.
(Just insert the crosstab query into  a blank report
User generated imageDatabase67.mdb
Avatar of JSneil
JSneil

ASKER

Thank you for your help