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 thousand 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. I use the count feature, and group by country and sum balance. As I want to know what the total balance by country is.

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

Can anyone help.

Cheers

Sharkiness

 

[DCrake]

In your query you need to group by Country sum balance and place an extra column in that equals

Cnt:1
and count this column
This will give you a total for each country, the name of the country and the balance for each country.

CodeMaster:

 

[Brianwarnock]

I don't understand codemasters answer.
I think that you will need 3 queries
q1 Groupby country and company
q2 groupby country and sum balance
q3 join q1 and q2 on country groupby country , sum, groupby, first (take your pick of) sumofbalance, and count company.

You may want to change the caption of the balance column.

Brian