Better Performance with Calculated Fields?

[hdudley]

Hi All,
My app manages quarterly reports received from 30-40 grantees. I've made a report that displays the date/time each report was received (up to 4 reports per grantee, for a total of up to 160 reports per quarter) for all 4 quarters. We need to see the number and percentage of grantees that submitted each report, the number and percentage who did not submit a report, and the number and percentage of late reports.

I'm using unbound controls in the report footer for each of these questions. My formulas work. Here are examples:

Reports Submitted:
count- Count([fieldname])
percentage- Count([fieldname])/count(*)

Reports not submitted:
percentage=1-(percentage of reports submitted)
Note: this last is a calculation that subtracts the value in the "reports submitted-percentage" control from 1.

Late Reports:
count- DCount("[fieldname]","qryname","[fieldname] > [datedue]&quot
percentage- DCount("[fieldname]","qryname","[fieldname] > [datedue]&quot/count(*)

If I've slightly missed the syntax here, it's okay. It works properly in my report.

The issue is: The last page of the report take a long time to load. Is there a better way to set this up in order to speed it all up?

Thanks for any help!!!

 

[pdldavis]

Hi, it's probably your Dcounts that are slowing you down. I had a similar problem that I handled by doing the summing in a query. Here's some Sql:

SELECT DISTINCT Sum(IIf([Mechanical]=-1 And [constneed]=-1,1,0)) AS [MechInsts], Sum(IIf([Mechanical]=0 And [constneed]=-1,1,0)) AS [ElecInsts]
FROM tblInventory INNER JOIN tblInstIndexSU ON tblInventory.tagNo = tblInstIndexSU.insttagnumber;

Hope that helps.

 

[hdudley]

Thank you for the suggestion, pdldavis. I'm not so good with SQL, relying mostly on Access's objects like the query builder. I'm going to try putting my calculations in a query and see if that speeds it up any. I really appreciate your help.

 

[pdldavis]

Hi, you don't need to be that great with sql. Simply create a new query, go to sql view and paste the above sql in.
Go back to design view and it will show you how the calcs were used.
Sorry, should have been a bit more clear. That should help you out.

 

[mp9]

If you must use DCount, you can speed it up by replacing a specific field to count with a wildcard so that

DCount("[fieldname]","qryname","[fieldname] > [datedue]")

becomes

DCount("*","qryname","[fieldname] > [datedue]")

Put simply, this works because Access doesn't have to search through each record for the specific field you want to count - instead it just counts the fact that it's found a record. The performance improvement is small but do it to all your DCounts and you might notice something.

Hope this helps.

http://www22.brinkster.com/accessory/

 

[hdudley]

Thanks to both of you! I can't try your suggestions until next week, but I'll get back to you about how it turns out. I *so*!!! appreciate your help!!!!