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
  • 197 Views
  • 4 Months Discussion Span
  • Latest Post Latest Post by EraserveAP

Dani AI

Generated

Quick, reliable way to get "companies per country" (unique companies) and accurate country balances without double‑counting.

The root cause is duplicate rows for the same company. As suggested, split responsibilities; gave a helpful SQL example for per‑company totals. Below are Access‑friendly steps that keep the work visible and easy to debug.

Create a distinct list of company/country pairs, then build aggregates from that list. Save each query so you can inspect results as you go.

-- save as qryDistinctCompanies
SELECT DISTINCT [Company], [Country]
FROM YourTable;
-- save as qryCompanyCounts
SELECT [Country], Count([Company]) AS CompanyCount
FROM qryDistinctCompanies
GROUP BY [Country];
-- save as qryBalanceByCountry
SELECT [Country], Sum([Balance]) AS TotalBalance
FROM YourTable
GROUP BY [Country];
-- combine counts + sums
SELECT c.Country, c.CompanyCount, b.TotalBalance
FROM qryCompanyCounts AS c
INNER JOIN qryBalanceByCountry AS b
ON c.Country = b.Country;

If you want country totals built from each company’s aggregated balance (so a company with many records only contributes once), first make a company‑level aggregation (group by Company+Country, sum Balance), save that query, then group that result by Country — this avoids double counting company balances.

Troubleshooting tips: normalize names in the distinct query (for example UCase(Trim([Company]))) to collapse case/whitespace variants; watch for NULL countries; use indexes on Company and Country if queries are slow; and always inspect the intermediate saved queries (qryDistinctCompanies and the company‑level aggregation) to confirm you’ve removed duplicates before you count.

Recommended Answers

All 3 Replies

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.