MS Access count unique records in subform

Question

I have a table that lists service providers (tbl_service_providers). I also have a table that contains services to customers by providers (tbl_services_by_providers).

I have created a form with a dropdown linked to the tbl_service_providers table. I then have a subform that shows information from the tbl_service_by_providers linked to the parent form via ServiceProviders. It looks like this

--Parent Form --
Service Provider [NorthWind Traders]

--Subform--
Customer name - Service - Cost - Due Date

The same customer could appear many times in the subform for each providers, however I would like a count on the parent form of the unique number of customers for that provider so for example if John Smith appears 3 times he is only counted once.

Answer 1

Do an aggregate or SELECT DISTINCT query of the table subform is based on that returns a unique record for each customer then use a DCount() in a textbox on the main form that pulls count of customers from that query for the provider. Be aware that domain aggregate functions can perform slowly on forms and reports.