Count Unique Records on Left Side of Join

[Joshann]

I have a query that has two tables that have a one to many relationship. I need to count the number of unique records on the one side. I saw a post where someone else had the same problem, and Pat Hartman said to remove the join to the many side. The problem is that I can't do that because I need to count the number of unique records on the one side based on criteria in the many side.

For example, let's say there's a table called Customers with a field called CustomerID and a table called Orders with a field called ProductName. Each customer can have many orders. What I want to do is count the number of customers (CustomerID) for a certain product name (ProductName).

Can anyone help?

 

[Jon K]

Attached is a small database taken from the NorthWind sample.

In it I have built a series of two queries to count the number of Customers who ordered Boston Crab Meat.

In Query1, I used the keyword DISTINCT in the SQL statement. Hope the queries help.

 

[Joshann]

Attached is a small database taken from the NorthWind sample.

In it I have built a series of two queries to count the number of Customers who ordered Boston Crab Meat.

In Query1, I used the keyword DISTINCT in the SQL statement. Hope the queries help.

I will try it. Thank you very much!