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?
Microsoft Access

Avatar of undefined
Last Comment
doe5

8/22/2022 - Mon
Jeffrey Coachman

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 Coachman

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
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.