Counting Distinct Values

[Access::Student]

Hey, I'm trying to create a count field but I'm having some problems because
of a lack of COUNT DISTINCT.

The calculate control is a form based on "Authorities", which has a primary
key of "kp_authorities_id". I want to count the number of entries on a query,
which has the two fields (among others) "kf_submissions_id" and
"kf_authorities_id" (both foreign keys). I want to count the number of
records that:

1. "kf_authorities_id" = "Me.kp_authorities_id"
2. "kf_submissions_id" is distinct

So essentially the number of distinct relationships to "submissions". I have
no idea where to start though. Any ideas?

 

[Access::Student]

Hey, so I'm pretty sure the following query works well:

SELECT Count([Financial Totals].kf_submissions_id) AS [Count]
FROM (SELECT DISTINCT [Financial Totals].kf_submissions_id FROM [Financial
Totals] WHERE [Financial Totals].[kf_authorities_id]=[kp_authorities_id]);

but now I'm not sure how to put the value into a control, since it needs to
request a value (kp_authorities_id) . Where would i do that?