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?
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?
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
http://support.microsoft.com/kb/328320
But an example of your source data and the exact output would make this easier to understand