I posted this in a forum somewhere; I figured I would repost here with pictures.

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.

 

SQL View is very handy in Reverse as well. 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.

Tags: MS Access Count Query MS Access Sum Query MS Access Count and Sum Query

MS Access, Reference ms access group by, count and sum query, ms access tutorials, ms access examples, ms access help, ms access