467,087 Members | 1,255 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,087 developers. It's quick & easy.

Count query counts records not companies


I'm new to using queries in Access and am stuck on trying to run a query from a table.

I have thousands of records with fields such as customer name, Company Name, Company Country and Balance. What I want to do is run the query to count the number of Companies within a country. As there are thousands of records one company may be in the table up to a couple hundred times and will be named a few times overs for each country. I use the count feature to count company, group by country and sum balance, as I want to know what the total balance is by country also.

The query is giving me the total number of records by country instead of number of companies per country. The balance sum seems fine.

Can anyone help.


Sep 26 '08 #1
  • viewed: 1318
4 Replies
Expert 2GB
Hello, Sharkiness.

Try 2-step grouping:
  • First query groups by company and country and sums balance.
  • Second query groups by country, counts company groups from the first one, sums balance subtotals from the first one.

Sep 26 '08 #2
You can also utilize the Distinct statement in SQL.
Sep 26 '08 #3
Maybe the simplest solution would be to add the field Company twice.
Country (Group by)
Company (Group by)
Company (Count)
This way you can do it in one simple query :-)
Sep 26 '08 #4
Expert Mod 16PB
As you have not described the circumstances under which a company may appear multiple times within a country it is not possible to be very specific.

Conceptually though, I'd use a mixture of Fish's and Kristilee's methods. On the inside have a subquery using the DISTINCT predicate of the SELECT clause. As a shell I would count the results.
Sep 26 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Hugo L. | last post: by
22 posts views Thread by MP | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.