Counting MANY different things is SLOW

[tyleri]

I am in dire need of help - I have to count almost 20 different criteria in 2 different fields.

I have one field called "specialist" and I need to count how many records each specialist has. I am currently doing this by making a seperate query for every specialist and throwing all the queries into a "master" query - but this is ridiculously slow.

I also need to count some things in other fields too so I can throw those numbers on a report. HOw can I go about doing this without having to wait close to 2 minutes for the query to execute?

 

[jimmythegeek]

To count how many records a Specialist has, simply create a query,
Bring in the Specialist field first, and the Key field second.
Now change it to a totals query (button on the toolbar that looks like a greek E)
In the total row of the key field, change the "Group By" to "Count"
Run it. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[turnerjs]

I am also trying to count the number of times a field contains a specific value.

e.g I have 100 records, For field1 there are 15 records containing the value 01, 20 records containing the value 02, 28 records containing the value 03, 32 records containing the value 00, etc.

Can I produce totals showing 01 occurs 15 times, 02 occurs 20 times, 03 occurs 28 times, etc. with a single query?

 

[turnerjs]

I have found a answer for my problem. I hope it helps you with yours.

Use the 'SUM' function in the 'total' line. In the field area convert the tested field value to a zero or one. The SUM will then tell you how many of the tested values were found

e.g. TestForValue05:iif([fieldA]=05,1,0 - the SUM will total the # of records with a value of 05 in field A.

TestForValue03:iif([fieldA]=03,1,0 - the SUM will total the # of records with a value of 03in field A.

etc.