Link to home
Start Free TrialLog in
Avatar of doe5
doe5

asked on

Access Report using Crosstab Query

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?
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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
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
Avatar of doe5
doe5

ASKER

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
Then as far as I can tell, the info in the links should help
Avatar of doe5

ASKER

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?
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
Avatar of doe5

ASKER

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.
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
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
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
Avatar of doe5

ASKER

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.