Big, slow report problem

[dcorey]

Hi!
I'm trying to make a report that compares the results of the tests of several samples in a lab. I have a form where the users enter the samples they want compared (up to, say, 10 total), and the report prints from there.

Now, the only way I could think of to get the data I need from the various tables was this: I based the report on the comparison table (which basically just lists the sample numbers for each comparison, with each comparison being a different record). I then made each sample number on the report be a combo box. The row source of each combo is based on a query that links the 3 or 4 tables that contain all the testing data with the appropriate sample number (so each of the 30,000 rows of the query lists a sample number followed by all its 30-some-odd test results -- yes, this is huge!). This way, I can reference the columns for each sample combo to populate the text boxes that show its results.

This works, but it's EXTREMELY slow. I guess that's what happens when the query needs to be run for every sample number combo box... yikes! Plus, referencing the combo box columns seems to turn the data into strings, which destroys my forced decimal places, and makes adding impossible. I can't help but think there must be a better way!

If anybody had the courage to read all this, any tips, big or small, would be greatly appreciated!
Thanks!

 

[Scott_S]

I do something similar but in a different way.

I created a local table (T_SamplesComparision) with one field in it which shares the same name as the field I wish to report on from my testing data(Sample #). I based a continious form on this table. Users can then enter the list of Sample #'s they require on this form which populates the local table.

Then in a Query have the local table linked to all your other tables that you need to query with a one to many link. Then build your report with the Sample # field as a group header, you can now put all you other test data in the detail section under each group header. You can then put a button to run the report on the bottom of the Continious form and print from there.

Should work a bit quicker I think.

I have then set up a form at the front which asks users if they wish to clear the Loacal table by having a Clear button that runs a Delete Query on the T_SamplesComparison table, otherwise they can click continue which just opens the form and displays the existing data.

If I lost you email me offgroup and I will try and explain better.

Regards

Scott
sspedding@banksiagroup.com.au