Count uniq records and assign to a control

S

Song Su

SID is the field of underline query of a form (have many duplicates). I want
to count number of uniq SID and assign the result to TotalStudent control on
the form. Please help. Thanks

- Song
 
M

Michael Gramelspacher

SID is the field of underline query of a form (have many duplicates). I want
to count number of uniq SID and assign the result to TotalStudent control on
the form. Please help. Thanks

- Song
Click to expand...
Maybe with a saved query such as
Query1: SELECT DISTINCT SID FROM UnderlyingQuery;

=DCOUNT("SID","Query1")
 
M

Marshall Barton

Song said:
SID is the field of underline query of a form (have many duplicates). I want
to count number of uniq SID and assign the result to TotalStudent control on
the form. Please help. Thanks
Click to expand...


You need a separate query to calculate a unique count.

query: DistinctCount
SELECT Count(*) As SIDcount
FROM (SELECT DISTINCT SID FROM formquery)

Then the TotalStudent text box can use the exression:
=DLookup("SIDcount", "DistinctCount")
 
Top