Search code examples
sqlms-accessms-access-2016

Combine Two Queries To One


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;

Solution

  • 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;