Member Avatar for 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. 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 companies. The balance sum seems fine.

Can anyone help.

Cheers

Sharkiness

  • 3 Contributors
  • 3 Replies
  • 164 Views
  • 4 Months Discussion Span
  • Latest Post Latest Post by EraserveAP
Member Avatar for timothybard

You are trying to do too much with a single query. You will need to make two queries for what you are trying to do. Here are the two queries you need:

1) Group by Company, Count on Company. The Company Field will be in the query twice; once to group by Company and once to Count by Company

2) Group by Country, Sum on Balance

If you want to know how many companies are in each country, you will need to add the country field to the first query and group by country. You will then need to wrap the first query with another query and count by company and group by country.

Member Avatar for Sharkiness

Cheers Mate,

Working a treat.

Member Avatar for EraserveAP

This is an old thread but here you go anyways…

Say you have a Table named 1A and Three Fields Named COUNTRY, COMPANY and BALANCE.
The following SQL Code Counts Each Company in each Country and Sums their Balance all in one Query:

SELECT [1A].COMPANY, [1A].COUNTRY, Sum([1A].BALANCE) AS SumOfBALANCE, Count("") AS SHOW_COUNT
FROM 1A
GROUP BY [1A].COMPANY, [1A].COUNTRY;


In MS Access you can copy/paste the SQL into a Query using SQL View. You can then write over the "1A"s with your own table name.

On Running the Query you will then Count each instance of a Company within a Country and Sum their Balances within said Countries.

To get a company’s Grand Total you would run the above as a Make Table Query and then run the Following on the New Table:
Use the Method above to paste into a new Query:

SELECT [1A].Company, Sum([1A].BALANCE) AS SumOfBALANCE
FROM 1A
GROUP BY [1A].Company;

A two step process for Totals of a Company World wide, a one step process for Totals of a Company per Country.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.