Count query counts records not companies

[Sharkiness]

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

[FishVal]

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

[Kristilee]

You can also utilize the Distinct statement in SQL.

[hjozinovic]

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 :-)

[NeoPa]

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.