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:
- How many counties belong to the region
- 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).