Struggling with Count Distinct in Microsoft Access Query

Question

I have a database of every sale my company has made for an entire year, there are 650,000+ records as it's divided by individual item and sale, which client made the purchase, and where it shipped.

I am trying to return a list of:

1. Distinct customers, without repeats as some have made many many purchases
2. A count of how many different states they've shipped to

The issue I'm running into is that I do get a distinct list of clients. Awesome. What I want. But then in my next column it counts every instance of a state... so our largest client shipped to over 43,000 states apparently. How do I solve this? I have attached images of the query I'm submitting

Answer 1

Hi Ryan,

Thanks for contacting us. It seems like you're encountering an issue with counting distinct states for each customer in a Microsoft Access query. The problem arises from the fact that you're getting a count of all states for each record instead of a count of distinct states per customer. To solve this, you can use a combination of SQL functions to achieve the desired result. Here's how you can approach it:

1. Grouping and Counting Distinct States per Customer:

SELECT CustomerName, COUNT(DISTINCT ShippingState) AS NumberOfStates FROM Sales GROUP BY CustomerName;

This SQL query groups the sales records by the "CustomerName" column and then counts the distinct shipping states for each customer.

2. Sorting by Number of States:

If you also want to sort the results by the number of states in descending order, you can add an ORDER BY clause:

SELECT CustomerName, COUNT(DISTINCT ShippingState) AS NumberOfStates FROM Sales GROUP BY CustomerName ORDER BY COUNT(DISTINCT ShippingState) DESC;

This query should give you a list of distinct customers along with the count of different states they've shipped to, sorted by the number of states in descending order.

I hope this helps.

Regards, Sola

Answer 2

To return the COUNT of DISTINCT values return the DISTINCT values in a subquery in the FROM clause of the outer query.  The following is an example which returns the distinct number of employers of persons resident in each city:

SELECT City, Region, COUNT(*) AS EmployerCount

FROM (SELECT DISTINCT Cities.CityID, City, Region, EmployerID

             FROM Regions INNER JOIN ((Cities INNER JOIN Contacts

             ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers

             ON Contacts.ContactID = ContactEmployers.ContactID)

             ON Regions.RegionID = Cities.RegionID)

GROUP BY City, Region, CityID;

The above query is taken from DatabaseBasics.zip, which you'll find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file, as its name suggests, provides an introduction to some of the basic principles and methodologies used in designing relational databases in Access, and includes a number of examples of commonly used query types.

Answer 3

This returns an error:

"Syntax Error (Missing Operator) in query expression COUNT(Distinct ship2_state)"

Answer 4

Access does not support the syntax which the other respondent gave you. You need to use a subquery in the FROM clause as I described.