Looking for faster group sums

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

Recently I posted a question on how slow my DCount()
function was working on a database with 1,500,000 records
and someone responded telling me to do this:

In the click event code on the form object:
---------------------------------------------------
Dim vRecordCount As Double
vRecordCount = retCount("requirements","...","...")

---------------------------------------------------
And in a module this was called:

Public Function retCount(TableName As String, WhereClause
As String) As Long
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As TotalRecords " & _
"FROM [" & TableName & "] " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSQL)
retCount = rs!TotalRecords
rs.Close
Set rs = Nothing
End Function
---------------------------------------------------
This was so much faster and makes me wonder if there is a
faster way for my question below.

This is on group totals. I have a query on the same
1,500,000 records where I am doing:

Field1 Field2 Field3 Field
GroupBy GroupBy GroupBy Sum

There are even a couple Fields with WHERE criteria and
this is way too slow.

Is there a method of grouping and totalling and being able
to call the results to a report that is faster than doing
it the way I am in the query grid. I was hoping for the
same pickup of speed as I found in the DCount() method
change above.

Thank you for your help.

Steven
 
There are even a couple Fields with WHERE criteria and
this is way too slow.
Click to expand...

Are there Indexes on Field1, Field2, Field3 and the fields with
criteria? If not, add them (nonunique unless you know the field to be
unique) - this will dramatically speed things up.
 
Back
Top