Report from a Crosstab Query

[morpheus]

I am having a time trying to figure out how to design a report that pulls its data from a Crosstab Query where the column headings change for every specific criteria defined. I am trying to make the report on a series of incremental QC test results that are performed a few thousand products. Some products might be tested at 1 hour, some at 10 hours, some at a week, etc... overall they is 12 different time increments... but they are not all used for every product!!!

I do not want to have to design 1700 different report depending on a product number (hahaahaha)... how can i just have the columns that are showing up through the crosstab query automatically go into the report... ie with out nameing a strict control source but letting it be more dynamic accordingly.

Thanks in advance.

 

[NigeP]

Sorry that I cant help you, but I asked for help on this same topic a few weeks ago with no reply. Please let me know if you solve this, even if it is not through this sight.
My email address is price_nigel@hotmail.com
What I have found out though is that there is some VB code that allows for this and can be found on your Office 97 CD under Sales Employees mdb. I think it may be under Northwind examples.
Cheers

 

[Pat Hartman]

There is a sample of a report with variable columns built from a crosstab in either solutions.mdb or rptsmp97.mdb

 

[morpheus]

Where could i find those files? The guys in the IS department did not install any Samples with any of the Office apps and from the looks of the file name they are from Office 97 anyhow.. i am using 2000.

Let me know if you can if there is another source... guess i need to go search for one right now..

Thanks again.

 

[morpheus]

um, Scratch that i found them after some scrounging around, appreciate the tip though. Thanks.

 

[morpheus]

Well i found the rptsmp00.mdb and reviewed the samples therein...however the one i think you are refering to is not working for me in this case. It is on how to eliminate whitespace by using the CanShrink and VBA Coding.
I tried the CanShrink command in this case. I used one of my columns as a test subject and gave it the property. Then i selected a record that i knew would not have that particular information but it still displayed the column and column heading. Am i doing something wrong here or is there something more complex that i am not aware off. Intially i thought i was being dense for not thinking about the CanShrink property but now i don't feel so bad...

Anyone give me a hand here?

Thanks!

 

[Pat Hartman]

The relevant sample is in solutions.mdb. In their infinite wisdom, Microsoft elected not to include this execellent sample database with A2K. If you find the A97 version, you can convert it.

 

[morpheus]

<Sigh> this is starting to be agravating. I noticed in some old posts that this particular problem was one that you experienced first hand.

Now that i am looking at this Solutions report and the underlay of very complex VBA, i am wondering if i will ever get this one to work.

For one, the Solutions example has date criteria, which i don't need. (Once i get this part right i can add the code that only brings up the last 30 records, right now this is not an issue.) When i tried to Rem out the lines dealing with the cate criteria i started having issues.

This is what we are looking at... three tables (tblMixes, tblPours, tblTests) are referenced together in the intial query (qryMixesToTest_pt1). This query is used then to pull my necessary data, it is then put through a crosstab query to get the proper column headings i need. I end up with three queries from this (qryMixesToCompressiveTest_crs, qryMixesToFlexureTests_crs, qryMixesToShrinkageTests_crs)...
these queries are then used as the source for three individual reports... the first of which is causing me all this irritation. Once i get this one done i can simply copy it for the last two reports.

the report is intiated by a command button on a subform (frmPours) on my main user form (frmMixes). The main reason for this is that i want the report to display ONLY test data related to the current by using the frmPours!MixID field as a criteria.

For the sake of formatting, i grouped the report being worked on by the MixID, so that if i wanted, i could do a none referenced version of this report.

When i originally was working on this report, I got the synchronization between the frmMixes and the rptBreakData set up rather quickly. This is a very important part of the database structure to set up. The report itself is group by the MixID rather than displaying it over and over on multiple lines of the report.

Would this Grouping cause the problems that i might be having or is this VBA code for this report just very very advanced for me to customize??