Count Unique Records

[patkeaveney]

I have a form bound to a query.
The query contains thee following tables

tblRequests
Field Name Value Value
intrequestID 1 2

Linktable
Field Name Value Value Value
intRequestID 1 1 2
intExemptionID 20 21 20

tblExemptions
Field Name Value Value
intExemptionID 20 21

In the code I want to count the number of unique intRequestIDs in the link table

In the above example the count would be 2.

I have used record count, but that returns a value of 3, which is right for the number of records, but how do I code to get the unique count.

Thanks for your help


Pat

 

[HiTechCoach]

Try using the SQL verb DISTINCT

 

[patkeaveney]

Can you post an example code of how to use this to obtain a count within a forms code.
I dont need to filter the records displayed in the form.
The user selects an exemption code from a drop down, and in this case the message displayed to show the number of records is correct, as the same exemption cannot be applied to a single request.
When the user selects "all" from the dropdown i want to display a message showing the number or records with exemptions applied), hence the logic of counting unique records, as a request can have more than one exemption applied.

 

[patkeaveney]

For anyone looking for a solution to this problem:
Here is an answer from another member. (qrySearchByExemption is the name of my origiinal query as detailed above)
Step 1:
Create a query as follows:
SELECT Count([intFOIRequestID]) AS RecCountAll FROM [SELECT DISTINCT intFOIRequestID FROM qrySearchByExemption]. AS RecCount

Step 2: Create a RecordSet to reteive the count.

Dim db As Database, rst As Recordset, qdf As QueryDef, prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySearchByExemption")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)

Step 2 provided by forum member WIS at link below

http://www.access-programmers.co.uk/forums/showthread.php?p=844658#post844658