Hi, I have a form that displays the results from several count queries. When I run each query individually they come back with a result instantly, so if I add 10 queries to the form I thought it would take 10 seconds max to display the results, but it takes minutes. Can somebody tell me why this is, and how to fix it?

Cheers,
Callum.

There are a lot of variables here. How are you running all of the queries from your form?

hmm, well the queries are run in the RecordSource before the form is displayed. It's confusing because if I run each query manually in succession then I can view all the results in 20 seconds (double clicking each query, clicking the 'x', running the next query etc). So I don't know why running them all together should increase the wait time so considerably.

How are your queries "run in the RecordSource"? what are you actually doing? Is it code in an event of the form? If so can you display it?

I'm not very good so I'll try to explain. I have many count queries similar to this one (one for Ethnicity=11, another for =12 etc):

SELECT Count(Learners.Ethnicity) AS CountOfEthnicity
FROM Learners
WHERE Learners.Ethnicity=11
AND Learners.CustID IN
( SELECT CustID
FROM [MonthlyUsage]
);


Where [MonthlyUsage] is a query that gives a list of CustID's as a result.

And others including ones that count the number of customers within age ranges using DOB (query for 18-29, query for 30-39 etc) and for male/female etc.

Then in a form, as the Record Source I've just dragged in the field 'CountOfEthnicity' for each variable (=11, =12, CountOfMale etc). And then in the form I have text boxes that display the values to create a statistics page of learners that have been active recently.

Many thanks in advance for any help, if I have explained it well enough =/

A form only has one RecordSource. How are you inserting these queries in the RecordSource of a form, and more importantly why use the RecordSource at all? Are you dynamically creating each of these queries or are they static queries created with the Query Wizard?

Hi, I might be using the wrong term. The queries are all static. Here's a screenshot of what I meant:

http://www.kendokitten.co.uk/Screenshot.png

That explains the speed problem. I would be curious to know what the SQL for that query looks like. You would be far better off simply using a RecordSet in the OnLoad event of your form to run each of those queries one at a time and put the results in the proper control on your form.

ahh yes! I can't believe I'd never thought about that before, the SQL for that will be ridiculous... Pitfalls of using Access really, if I was doing the same sort of thing outside of Access I would never have dreamed of writing an SQL statement to handle that; but the the ease of setting up things in Access this way didn't make me think twice haha.

Cheers for the tip, I will try it in the morning, though I've never used a RecordSet before I'm sure it'll be quite simple.

It is pretty easy. Post back here if you need further assistance.