Dynamic crosstab query based report

[JoeCruse]

Searching this forum has helped me today with with an issue on dynamic reports based on crosstab queries. I recently revised some of my lab app, to better normalize the tables/data structure. This changed my reporting method, and I was really sweating over one lab test report. It meant going with a dynamic crosstab query report, with no option for fixed columns in the query because of the possible number of columns to be returned (over 1000). Using the sample in the Solutions app, I finally put together a working system, but it was pretty slow. Running off a BE residing on a network drive, it took over 30 seconds for a 1-page report, pre-filtered for less than 40 records, to come up. This is unacceptable, and I was losing hope until I did a search here and found this thread:

thread703-922818

My thanks to DHookum for his solution to this problem. It took some study and an adaptation to one table, but with that done, the report works just fine. A very ingenius solution to a tough problem.

 

[dhookom]

Joe,
Thanks for the compliment. I am currently vacationing in Washington DC and read your message aloud to my wife and sister. My sister immediately bowed down to me in reverence Apparently my wife doesn't share my sister's opinion.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeCruse]

Apparently my wife doesn't share my sister's opinion."

Yeah, you'll get that occasionally

The only kicker with your solution is figuring out one's own setup for "assigning" column placement values to the needed values in the right table. Once you wrestle that down, the rest is cake. I've been fighting with the Solutions App variation on the dynamic report for a month, off and on. If the report wasn't so slow, it would be a great solution. I'd like to know why it takes so long to run on a query that runs in a couple of seconds.

Thanks again.