Problem with count results in query with two counts

[mmgio413]

I am not an Access expert, but I do basic SQL querying in my job using a different tool. Now, I have a small database I have created in Access, and I am working on a new query that is a bit more advanced for me. I have never had 2 different columns doing a count function at once. In this case, both counts are coming from a different "Select distinct" query. The problem I am having is they are being multiplied. I was wondering if anyone is able to tell me what I am doing wrong. Here is my query so far:

SELECT
RG.REGION_ID,
RG.REGION_NAME,
RG.DATE_ASSIGNED,
RG.DEADLINE,
RG.REGION_STATUS,
Count(C1.COUNTY_ID) AS COUNTIES,
Count(LOC.EMAIL) AS LOCALS
FROM REGION AS RG,
(SELECT DISTINCT RG.REGION_ID, CN.COUNTY_ID FROM COUNTY AS CN, REGION AS RG WHERE RG.REGION_ID = CN.REGION_ID GROUP BY RG.REGION_ID, CN.COUNTY_ID) AS C1,
(SELECT DISTINCT CN.REGION_ID, LC.EMAIL FROM COUNTY AS CN, LOCALS AS LC WHERE CN.COUNTY_ID = LC.COUNTY_ID GROUP BY CN.REGION_ID, LC.EMAIL) AS LOC
WHERE RG.REGION_ID = C1.REGION_ID
AND LOC.REGION_ID = RG.REGION_ID
GROUP BY RG.REGION_ID, RG.REGION_NAME, RG.DATE_ASSIGNED, RG.DEADLINE, RG.REGION_STATUS;

I first ran both of those "select distinct" queries as separate queries, and they return correct values.
Here is the "C1" query on it's own:
SELECT COUNTIES.REGION_ID, Count(COUNTIES.COUNTY_ID) AS COUNTIES
FROM (SELECT DISTINCT REGION.REGION_ID, COUNTY.COUNTY_ID FROM COUNTY, REGION WHERE REGION.REGION_ID = COUNTY.REGION_ID GROUP BY REGION.REGION_ID, COUNTY.COUNTY_ID) ASCOUNTIES
GROUP BY COUNTIES.REGION_ID;
This query returns the number of counties in each region. As an example, region 18001 returns 2 counties.

Here is the "LOC" query on its own:
SELECT REGION_ID, COUNT(EMAIL) AS LOCALS
FROM (SELECT DISTINCT COUNTY.REGION_ID, LOCALS.EMAIL FROM COUNTY, LOCALS WHERE COUNTY.COUNTY_ID = LOCALS.COUNTY_ID GROUP BY COUNTY.REGION_ID, LOCALS.EMAIL)
GROUP BY REGION_ID;
This query returns the number of local specialists that work in each region. Local specialists are assigned to counties, but my COUNTY table contains Region_ID, as well). The same region, 18001, has 9 local specialists (9 in one county, and 4 in another, but 9 unique ones).

When I run the entire query together, region 18001 is returning a value of 18 for both the counts: Count(C1.COUNTY_ID) and Count(LOC.EMAIL).

Clearly, it's multiplying them. But, I can't understand why. What have I done wrong?

I appreciate any help I can get!



__________________________________________________ _______________________
In case any additional background information is helpful, regarding what I am trying to do:

• My database has 4 tables: Region, County, Roster, and Locals
  
  • Region contains basic information about each region. ID, name, status, notes
  • County contains information about each county, including an ID, the region it belongs to, number of tickets, number of tickets in various statuses, and notes
  • Roster includes a list of local specialists and employees. Each employee is listed just once.
  • Locals is a list of local specialists and the counties they work in. A local specialist that works in 15 counties will be listed 15 times.
  
  
  
  
• I am creating a query that is a regional summary. The regional summary needs to show some basic regional info off the Region table, as well as info rolled up from other tables.
  
  
  • Right now, I'm just working on adding these 2 "count" columns as part of the summary. There will be more, eventually. These should tell me:
    
    
    1. How many counties belong to the region
       
    2. How many (unique/distinct) local specialist are assigned to counties within that region (so, a single local specialist might be assigned to 5 counties in a region, but should be counted only once).

[mmgio413]

UPDATE:

I think I managed to get around this problem by saving the individual select distinct's with the count fields as individual queries, and then building those into my query:

SELECT
RG.REGION_ID,
RG.REGION_NAME,
RG.DATE_ASSIGNED,
RG.DEADLINE,
RG.REGION_STATUS,
C1.COUNTIES,
LOC.LOCALS
FROM REGION AS RG,
COUNT_COUNTIES_REG AS C1,
COUNT_LOCALS_REG AS LOC
WHERE
RG.REGION_ID = C1.REGION_ID
AND LOC.REGION_ID = RG.REGION_ID
GROUP BY RG.REGION_ID, RG.REGION_NAME, RG.DATE_ASSIGNED, RG.DEADLINE, RG.REGION_STATUS, C1.COUNTIES, LOC.LOCALS;

If anyone sees this, please let me know if this was the best way to do it, or was there a better way?