Count of distinct records in textbox 1

[MePenguin]

Any way I can get this SQL to work like =DCount as the control source for a text box?

select count(*) as countofdistinct from (SELECT DISTINCT TFossil.CODE, TSample.CountsheetCODE
FROM TSample INNER JOIN TFossil ON TSample.SampleCODE = TFossil.SampleCODE
GROUP BY TFossil.CODE, TSample.CountsheetCODE
HAVING (((TSample.CountsheetCODE)="coun000030")));

where coun000030 is CountSheetCode extracted from the form as in the DCount below.

...or should I just save it as a query and dcount it? Something like:

=DCount("*","QSelectTDistinctFossilsWithCountsheetCode","[CountsheetCODE]= '" & [BoxCountsheetCODE] & "'")

I've heard dcount can be slow - could this be a problem for 50000-200000 records?

I need this value to be shown on a Continuous Forms, which is a subform filtered by CountSheetCode.

Thanks,




Phil

---------------
Pass me the ether.

 

[MePenguin]

It is slow! I have three of these dcounts on the subform, takes about 2-4 seconds to evaluate, which is a bit too long for my impatient users.

Phil

---------------
Pass me the ether.

 

[TheAceMan1]

How are ya MePenguin . . .

Sure! . . . just put the SQL (not sure about your SQL) in a function and use a recodrset to return the count!

In the code module of the form, copy/paste the following function:

[blue]Public Function DistrictCnt()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT COUNT(*) as CountOfDistinct " & _
                "FROM (SELECT DISTINCT TFossil.CODE, " & _
                "TSample.CountsheetCODE " & _
         "FROM TSample " & _
         "INNER JOIN TFossil " & _
         "ON TSample.SampleCODE = TFossil.SampleCODE " & _
         "GROUP BY TFossil.CODE, TSample.CountsheetCODE " & _
         "HAVING (TSample.CountsheetCODE = [red][b]'[/b][/red]" & [purple][b]Me!CountsheetCODE[/b][/purple] & "[red][b]'[/b][/red]);"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   DistrictCnt = rst.RecordCount
   
   Set rst = Nothing
   Set db = Nothing

End Function[/blue]

The [blue]ControlSource[/blue] of the textbox becomes:

[blue] =DistrictCnt()[/blue]

Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

. . . and no aggregate delays!

See Ya! . . . . . .

 

[MePenguin]

Great tip! Have a star

Teaked it a bit, and now it runs like lightspeed.

Public Function FunCountDistinctSpp()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   
   SQL = "SELECT DISTINCT TFossil.CODE, TSample.CountsheetCODE"
   SQL = SQL & " FROM TSample INNER JOIN TFossil ON TSample.SampleCODE = TFossil.SampleCODE GROUP BY TFossil.CODE"
   SQL = SQL & " , TSample.CountsheetCODE HAVING (((TSample.CountsheetCODE)='" & Me!CountsheetCODE & "'));"
     
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   FunCountDistinctSpp = rst.RecordCount
   
   Set rst = Nothing
   Set db = Nothing
End Function

Thanks again AceMan.

Phil

---------------
Pass me the ether.