Order the results of four individual queries that return a count

[Roy33]

I have an access DB that tracks sales activity. My goal is to generate a report that 1) displays the total number of transactions for each of 4 states, and 2) orders the report dynamically in order highest number of sales to lowest.

For now I'm working on part 1 (db structure below)..

I have 4 basic queries that list the transactions for each of 4 states (e.g. pull all the transactions that are from new york).

I then have 4 other queries, based on the above queries that count the records (e.g. count the records in the new york table).

I've gotten this far so that I have four queries, with each query having the correct # of records.

This is fine, but I can't seem to find a way to get a query that can display the data like this:

New York 56
New Jersey 50
Rhode Island 44
Mass 42

The db is designed as follows (simplified)
tbl_states:
ID state
1 New York
2 New Jersey
3 Rhode Island
4 Massachusetts

tbl_sales
ID_sale sales rep state (a lookup column)
1 joe 1
2 mike 1
3 jen 3
4 george 4

I'm trying with a count but I can't seem to pull a second table into the select statement because of an aggregate function error -- on the other hand I can't just use the count by itself because part of the data is from a join.

Thanks in advance for any advice!

 

[raskew]

Here are a pair of queries which you can just plug into Northwind. Realize it's not precisely what you're after, but they provide food for thought.

The first (Query67) counts the number of orders by employee, by state.

The second (Query67_Crosstab) displays the information.
Query67:

SELECT Orders.ShipRegion AS State, Count(Orders.CustomerID) AS CountOfCustomerID, Employees.LastName
FROM (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.ShipRegion, Employees.LastName, Orders.ShipCountry
HAVING (((Orders.ShipCountry)="USA"))
ORDER BY Orders.ShipRegion, Count(Orders.CustomerID) DESC;

Query67_Crosstab:

TRANSFORM Sum(Query67.CountOfCustomerID) AS [The Value]
SELECT Query67.State, Sum(Query67.CountOfCustomerID) AS [Total Of CountOfCustomerID]
FROM Query67
GROUP BY Query67.State
PIVOT Query67.LastName;