Access Report using Crosstab Query

doe5
doe5 used Ask the Experts™
on
Using Access 2010 - I have a crosstab query that will return a variable number of columns each month based on the criteria --- the columns are months which project the number of orders needed for a particular unit based on the order month.   So in april ... I may have projected orders for may, june, july .... but in june I may have projected orders for june, july, aug, sep, oct, nov, dec.

The crosstab query works but not the report I've based off of the crosstab query ... all columns may not be reported or an error is generated such as "[Aug] is not recognized as a valid field name".   I don't want to recreate the report each month.


How can I allow for a variable number of columns in the report with changing field names so the report will run correctly each month from the crosstab query?   Or please suggest a better way to create the report.

And I would also like for the report columns with heading of month in format mmm to be sorted by month order (1-12) rather than alphabetically --- anyway to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
See here:
http://support.microsoft.com/kb/328320

But an example of your source data and the exact output would make this easier to understand
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Remember, a report in MS Access will "hard-code" the fields.
So creating a report where the fields are created "Dynamically" is tricky.

There are other workarounds as well, You can Google:
    Microsoft access crosstab query report

Author

Commented:
Thank You.  I'll check out your suggestions .... here is a sample the output I have ..  

last month was this ...  

Prod           April 2012    Jul 2012    Jun 2012   May 2012   Total
45678T               1                                  2                              3
3256U                2                1                                 2             5

This month is

Prod             Aug 2012   Jul 2012      Jun 2012      May 2012    Sept 2012    Oct 2012  Total  
45678T              1                                  2                                         3                6           12
3256U                                  1               1                   1                                                     3
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Then as far as I can tell, the info in the links should help

Author

Commented:
it will take me little time to see if google can help with the solution ... it would be nice if someone with some experience with this could direct me to good solution .... Anyone?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
Google will present the two links I posted as some of the first hits.

All techniques to do this are a bit complex due to the nature of reports and crosstab queries.

<it would be nice if someone with some experience with this could direct me to good solution >
I just did....
I always use the technique listed in the MS Link and it works fine for me.
...all the steps to "direct you to a good solution" are in the link.

Sorry, no easy solutions here...

;-)

JeffCoachman

Author

Commented:
Okay I'll take some time if 'googling' can answer all my questions.  I appreciate your response.  I'll need a bit of time but I won't forget about the question.
ok this a bit easier than everyone is making out.

First build your crosstab using data from a union query.
The union query is all your data unioned with a dummy record which by chance happens to include all field types even those not currently used this month.

Finally make sure that you report filters out the Dummy record

I have done this before, but I think you will work it out yourself.
Based on your description you should be fine.

It might be worth building a specfic table with the records necessary to generate all possible outcomes, and you could prepare it with a suitably odd sounding name like tblCrossTabAllOutcomes
and design it in exactly the same way as your current query / table is laid out.
then the input of your crosstab will look something like:

Select *,'Main Data' as Tag from tblDoesData
UNION *,'Dummy Data' from tblCrossTabAllOutcomes
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
JanChanalytics,

I have seen the dummy record approach before.

The issue is that perhaps "all the fields" may not be known when the query is run.
...or even that all possible months may not be needed.

In this case the OP stated that they may have a need only for: "april may, june, july",
In other words, they may not need all the months of the year.

So if the OP does not mind having all months (and all the unneeded ones being empty), then this should be OK, ...
But the report output may questioned with 4 months of data and 8 blank columns.
;-)

So it might end up that they would have to create a dummy record each time the query is run to get just the results they need.

Indeed, what I often do is to create a report with all Months (all columns), then use code to "hide and condense" the un-needed months.
(so there are no gaping holes in the fields, if a month is missing.  In my code, I allow the option: "Show all Fields")

So here, to make something "easy" is a question of how much work you are willing to do ahead of time.
In the case of the MS link I posted, again, it hadcodes the report with the 9 employees.
Making the solution "easy", as long as the number of employees remains at 9.
;-)
(and you can stomach doing all the work ahead of time)

The same with this link:
http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm
...easy...
Once you set it all up...

The same is true for you technique or mine.

The bottom line is that crosstabs are just not report-friendly.
And any technique will probably fail as soon as the "fields/columns" are re-defined.
;-)

JeffCoachman
Can you upload a stripped-down version of your database?

Author

Commented:
In case there is any interest .. The solution I ended up using is from Duane Hookom at http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm.  This is a fairly simple solution to use dates that will change each month for the column headings in a report based on a crosstab query.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial