Need Help Summing UNIQUE Records

[Millerguitarworks]

Hi there--I'm pretty new to Access, so hopefully this should be a simple fix:



This DB is meant to manage and report clients and their associated investments. I have a CLIENTS table, and a PROGRAMS table (think "products') with a SUBSCRIPTIONS (think "orders") table as a junction between them. There is a yes/no field on the Clients table to indicate "Accredited Status". Yes means the client is accredited, and No means he is not.

By default, there can only be one product per order, but obviously a single customer can have multiple orders of the same product. For example, the SUBSCRIPTIONS table might look like this:

ID ClientID ProgramID QTY SubscriptionType

1 Smith A 1 IRA
2 Smith A 2 Trust
3 Jones A 1 IRA

I have a report that summarizes the SUBSCRIPTIONS table, grouped by ProgramID. Let's assume that both Clients' accredited status is "Yes". I need a field that counts the number of unique ClientIDs with a "Yes" value in the Client's Accredited Status field.

Using the example table above, the count should be 2 since Smith is the same client, but so far, I can only get it to give me an output of 3 since there are 3 subscriptions. Can anyone assist me with this conundrum? Thanks!

[Millerguitarworks]

Can anyone help? I'm still stuck.

[Millerguitarworks]

My clients and investment programs have a many-to-many relationship, so I have 3 tables--CLIENT, PROGRAM and SUBSCRIPTIONS with SUBSCRIPTIONS as the junction table between the other two. One client can have multiple subscriptions to the same program. It is this situation which is proving troublesome for me...

Ultimately I need a report/query to Count the unique occurences of ClientID (foreign key) in the SUBSCRIPTIONS table for any given Program. So far, I can only get it to output all occurences. Can anyone suggest a way of eliminating duplicates from the count? Thanks!

[RuralGuy]

Have you looked into the DISTINCT verb?

[Millerguitarworks]

Thanks Rural. I was not aware of the distinct verb, nor had I written any code until today, but on your suggestion I was able to find some example queries using the distinct parameter, and have used them to successfully build my query and related report.

Sorry for the duplicate posts--I wasn't getting any responses!

[RuralGuy]

Since we are all volunteers, it is usually proper to give us at least 24 hours to catch the new thread. It is also not *too* abusive to BUMP the thread by posting to it again, as long as it is not done too often. Starting another thread just confuses the issue.