By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Count query counts records not companies

P: 19
Hi,

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.

Cheers

Sharkiness
Sep 26 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
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.

REgards,
Fish
Sep 26 '08 #2

P: 2
You can also utilize the Distinct statement in SQL.
Sep 26 '08 #3

100+
P: 167
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

NeoPa
Expert Mod 15k+
P: 31,307
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.