Dynamic Multiple Value Crosstab Query

[barrysprout]

I have created a number of crosstab queries which I can join with a select query to produce a multiple value crosstab as per the standard method. The output I want is as follows:

Product Country1 Country1 Country2 Country2 etc
Sales Budget Sales Budget

The problem is that I want to run a report based on this final query but the number of countries varies, depending on a criterion selected on a form, i.e. the user selects a world region and the number of countries in each region varies. So the final query on which the report is based needs to be dynamic. Has anyone tackled this kind of problem before?

 

[LonnieJohnson]

You can put unbound text boxes on your report. Let's say you have 15 Countries but may only show 10 if you run the report one time and 12 the next time. What I do is set the 15 boxes on my report to invisible. Then using a query def I determine how many fields are in the query and set my boxes visible and also assign them a control source at that time.

I hope this sheds a little light on your situation.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[barrysprout]

Thanks but the problem I have is how to join 4 crosstab queries with a select query when you don't know which countries the crosstabs will throw up. So the country fields in the final select query are unknown until the crosstabs run. Each crosstab computes a different value e.g. cumul sales or orders etc for the same range of countries (which vary depending on the region selected). I'm thinking of building the final select sql string dynamically but perhaps there's an easier way.

 

[LonnieJohnson]

That's no problem that you don't the field names. That's where the query def comes in. You would iterate through the list of fields and get the field names at that point. Then assign the unbound text boxes on your report the name of the field as the control source.

I have done this many times. I will try to put together a small model for you. Go to my web site below and email me so that I can send it to you.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[barrysprout]

Thanks for sending your model however unless I’m missing something, it hasn’t really solved the problem. Your report is based on a single crosstab query. My report is based on a select query that gets its field names from four crosstab queries linked by product.

Crosstab 1 – cumul sales for (x) countries depending on region selected
Crosstab 2 – cumul budget for (x) countries depending on region selected
Crosstab 3 – cumul sales last yr for (x) countries depending on region selected
Crosstab 4 – cumul orders for (x) countries depending on region selected

The select query joins these crosstabs to produce the multiple value crosstab report.
Since the countries in the crosstabs vary, the country fields in the select query are unknown until the crosstabs run.

I can’t see a way of adapting your model to deal with this. I’ve managed to solve the problem by building the select statement of the final select query dynamically by running another query first that gives me the relevant country names. I then cycle through the records concatenating each country to the select statement (inserting each crosstab name). Once the final select query is built, I can then use your model to produce the dynamic report. Maybe there’s a simpler way?

 

[LonnieJohnson]

My sincere apologies. My solution ignored your issue of determining the fields of the first four crosstabs. I was only focusing on the report.

I am glad that you figured it out.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!