In Access - I'd like to show ONE query that provides the user with the results of BOTH of these queries so the query results are State, CountOfSP, CountOfUsers
Currently to get the information we run two separate queries of
Select State, Count(userID) As CountOfUsers
From SalesData
Group By State
Order By State;
Then our second query is
Select [%$##_Alias].State, Count(salesPerson) As CountOfSalesPerson
From
(
Select distinct salesPerson, State
From salesData
) AS [%$##@_Alias]
Group By [%$##@_Alias].State
Is there a way in access sql to combine these two queries so I can get all the data I need form ONE query?
EDIT
I tried this syntax, but it gives me largely inflated numbers and the exact same count for CountOfUsers and CountOfSalesPerson
SELECT salesData.State, Count(salesData.userID) AS CountOfUsers, C
ount(Regioninfo.salesPerson) AS CountOfSalesPerson
FROM salesData
LEFT JOIN
(
Select distinct salesPerson, State
From salesData
group by Region, salesPerson
) AS Regioninfo ON salesData.State = Regioninfo.State
GROUP BY salesData.State
ORDER BY salesData.State;
you can join them like this:
Select State, Count(userID) As CountOfUsers, Count(stateinfo.salesPerson) As CountOfSalesPerson
From SalesData
left join (
Select salesPerson, State
From salesData
group by salesperson, state
) AS stateinfo on salesdata.state = stateinfo.state
Group By Region
Order By Region;
another way:
Select State, Count(userID) As CountOfUsers, stateinfo.c As CountOfSalesPerson
From SalesData
left join (
select count(z) as c, state
from (
Select distinct salesPerson as z, State
From salesData
) as x
group by state
) AS stateinfo on salesdata.state = stateinfo.state
Group By Region, stateinfo.c
Order By Region;