CODEWITHSUNDEEP
sqlms-accessgroup-byinner-join
tmolloy8
tmolloy8

Reputation: 41

How to join two queries with different GROUP BY levels, leaving some records null

In MS Access, using SQL, I've combined two queries with inner join that both require the user to input a Start Date and End Date range. The first query (query 1) lists the count of how many people have left the program, the situation with which they left, the month, and the year they left. It is grouping the Count function first on the year, then month, then the leaving situation (which can be any of 5 options), which means that there are multiple records for each month (but not necessarily the same number of records for each month).

The second query (query 2) counts the number of people we've admitted to the program, the month, and the year. It is grouped first on the year, then month. So, with this query, there is only one record per month.

My inner join combines the queries correctly, except that it repeats query 2's values multiple times, depending on how many records each of query 1's months have. Is there a way to have query 2's values only listed once per month, therefore leaving the rest of the records for that month null?

Here's query 1:

SELECT Count(clients.ssn) AS CountOfDepartures, clients.[leaving situation], a.monthname, a.year1, a.month1
FROM clients INNER JOIN (SELECT month(clients.[departure date]) AS Month1, year(clients.[departure date]) AS Year1, months.monthname, clients.ssn FROM clients 
INNER JOIN months ON month(clients.[departure date])=months.monthnumber WHERE clients.[departure date] BETWEEN [Enter Start Date] AND [Enter End Date])  AS A 
ON clients.ssn=a.ssn
GROUP BY a.year1, a.monthname, clients.[leaving situation], a.month1
ORDER BY a.year1 DESC , a.month1 DESC;

Here's query 2

SELECT Count(clients.ssn) AS CountofIntakes, b.monthname, b.year2, b.month2
FROM clients 
INNER JOIN (SELECT month(clients.prog_start) AS Month2, year(clients.prog_start) AS Year2, months.monthname, clients.ssn 
FROM clients INNER JOIN months ON month(clients.prog_start)=months.monthnumber WHERE clients.prog_start BETWEEN [Enter Start Date] AND [Enter End Date])  AS B 
ON clients.ssn=b.ssn
GROUP BY b.monthname, b.year2, b.month2
ORDER BY b.year2 DESC , b.month2 DESC;

Here's how I combined them, but it gives me the repeating values:

SELECT countofdeparturesbyleavingsituationmonth.countofdepartures, countofdeparturesbyleavingsituationmonth.[leaving situation], countofdeparturesbyleavingsituationmonth.monthname, countofdeparturesbyleavingsituationmonth.year1, countofdeparturesbyleavingsituationmonth.month1, countofintakesbymonth.countofintakes
FROM countofdeparturesbyleavingsituationmonth 
INNER JOIN countofintakesbymonth ON (countofdeparturesbyleavingsituationmonth.monthname=countofintakesbymonth.monthname) AND (countofdeparturesbyleavingsituationmonth.year1=countofintakesbymonth.year2) AND (countofdeparturesbyleavingsituationmonth.monthname=countofintakesbymonth.monthname)
ORDER BY year1 DESC , month1 DESC;

The CLIENTS table has a record for each client with a bunch of columns for different clinical data (I work for a non-profit drug/alcohol rehabilitation center). The MONTHS table just have the twelve months written out in one column with a corresponding number in the second column. I use the inner join with the MONTHS table in order to list the monthname rather than the number (though I just realized that I could probably just do this in my report with the MonthName function....). Any advice is very appreciated! Also, I'm asking this question because when I make a report on this query the SUM value for the total report (and per calendar year) for the intakes is incorrect because, in the query, each month has multiple intake values. So the SUM is much larger than it should be. Beyond that issue, this query generates the correct report.

Upvotes: 2

Views: 1822

Answers (1)

fthiella
fthiella

Reputation: 49049

You could use DISTINCT clause:

SELECT DISTINCT
  queryA.people,
  Null as situation,
  queryA.[month],
  queryA.[year]
FROM
  queryA INNER JOIN queryB
  ON (queryA.people=queryB.people)
     AND (queryA.[month]=queryB.[month])
     AND (queryA.[year]=queryB.[year])

Upvotes: 1

Related Questions