DCount or Query most efficient

[detrie]

I am trying to determine if a DCount function would be more efficient than a query to return the count the items in a table.

I got the SQL to work but I am struggling with the correct syntax for the DCount statement because of the joined table. I am able to get this simple function to work but it is only looking at an ID rather than the LEFT 6 of the form field

If DCount is more efficient, I could use some help with syntax

Here is the SQL

SELECT tbl_SampleGroups.GroupNumber, Count(tbl_Samples.GroupID) AS CountOfGroupID
FROM tbl_Samples INNER JOIN tbl_SampleGroups ON tbl_Samples.GroupID = tbl_SampleGroups.SampleGroupID
GROUP BY tbl_SampleGroups.GroupNumber
HAVING (((tbl_SampleGroups.GroupNumber)=Left([Forms]![frm_Login]![GroupNumber],6)));

Here is the DCount function

DCount("[sampleid]", "tbl_Samples", "[GroupID] = 7")

 

[MarkK]

Those are not equivalent queries. The SQL joins two tables using an INNER JOIN, so if there are no child records, the parent record will also not appear (and not be counted). The DCount(), by contrast, only queries a single table, so even if the criteria was the same, the outcome could be different. If you need to count the result of the joined query, then you can't use DCount(), OR you can save that query, and use DCount() on the saved query.

 

[detrie]

Thanks Mark
Would the DCount (based on a saved query be more efficient (run faster) than the SQL statement?

 

[MarkK]

There's not going to be a big time difference if you run your own SQL or use a domain aggregate function. The same amount of work has to get done either way. The major impacts on speed are going to be the size of the table, the indexed fields, things like that. A domain aggregate function has to do the same amount of work with the same resources as your SQL. If you search a non-indexed field in a big table, your SQL and the equivalent domain aggregate function will both be slow.