Switching DAF's for queries (AC2007) (1 Viewer)

[AOB]

Hi guys,

I have a switchboard form which has a kind of dashboard element which tallies the counts of records according to a variety of pre-defined queries.

At the moment I am just using domain aggregate functions to return the counts. So the Control Source for one of the textboxes in the dashboard would look like :

=DCount("[ID]","[qryPending]")

So the textbox basically shows the number of records in that query

However, there are a number of these and it refreshes every 60 seconds or so. It's reasonably quick but there is a noticeable lag of a second or two every time it recalcs.

I'm wondering if I would be better off replacing all these DAF's with purpose-built queries to return the integer value. So using the example above, create a query that looks like :

SELECT Count(qryPending.ID) AS NumberPending
FROM qryPending

Call it "qryCountPending" and then use that as the Control Source instead?

So I guess I have two questions:

• will this be in any way quicker? (I have it in my head that hard-coded queries are faster than DAF's but I could be wrong)
• if so, how do I reference it in the Control Source? Given that these queries will return a single record with a single field, but how does the Control Source "know" that (usually, you have to include some kind of WHERE clause so it knows which record to return)

Thanks

Al

 

[plog]

if so, how do I reference it in the Control Source?

That's the problem, even if the query turns out to be faster, you'd still need a Domain function (Dlookup) to get that value to the form.

With that said, you say it refreshes every 60 seconds? Do you want that to happen? If so, you could do the refreshing behind the scenes--use an UPDATE query to move the totals to a table, then do a Dlookup into the table. That would speed up noticeable performance, not actual--you still have to run the queries; but to the user it wouldn't seem like the lag existed.

 

[AOB]

Thanks plog

Yeah, that was where I was falling over. Is there no way around this then?

 

[plog]

I might have edited my post as you responded. See my second paragraph about moving the totals to a table and doing a DLookup to the tabel.

 

[AOB]

Think so - okay thanks for that suggestion plog, let me delve into that a bit further...

 

[Minty]

You could create a query that pulled in all the results from your different queries, then use that query as your record source for the form. You wouldn't need the DLookups or DCounts

I'm not sure it would be any faster though.