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.