DCount

[skinny220]

I have a form with 3 Dcount expression basically counting the number of records from three different queries. Is there a way I can have the Dcounts run the biggest query first to speed up time vs all three DCount running at the same time.

 

[Joe4]

Don't know about that. Using Aggregate Queries may be an option.

Are you using Criteria in your DCOUNT calculations? If so, it may help to make sure that those fields are Indexed.

Otherwise, you may want to look at something like this:
MCount - Faster than DCount? - Access World Forums

 

[skinny220]

Joe,

I have the Dcount pulling the smallest value in the query and for some reason its taking over a minute to count 961 records. I will go back to the original table and index a few fields to see if that improves performance.

 

[Joe4]

Can you post your DCOUNT statement?
Do you have a Primary Key in your table?

 

[skinny220]

Joe will do but I think I found out the issue. I used the query wizard (below) to build a mismatch query, the query takes about to minute to calculate. I will go back and index the original table to see if it will help with performance.

SELECT [PBUSE CPL Q (SRRC)].LIN, [PBUSE CPL Q (SRRC)].SUBLIN, [PBUSE CPL Q (SRRC)].NSN, [PBUSE CPL Q (SRRC)].[NSN Nomenclature], [PBUSE CPL Q (SRRC)].PBIC, [PBUSE CPL Q (SRRC)].UIC, [PBUSE CPL Q (SRRC)].[SER|DETECT SN|REG|LOT|SYS NO]
FROM [PBUSE CPL Q (SRRC)] LEFT JOIN [MAT_SIT SN Q (SRRC)] ON ([PBUSE CPL Q (SRRC)].UIC = [MAT_SIT SN Q (SRRC)].UIC_HELP) AND ([PBUSE CPL Q (SRRC)].[SER NO] = [MAT_SIT SN Q (SRRC)].[SER NO])
WHERE ((([MAT_SIT SN Q (SRRC)].[SER NO]) Is Null))
ORDER BY [PBUSE CPL Q (SRRC)].LIN, [PBUSE CPL Q (SRRC)].UIC;