Hi everyone! So I need to build a query and based on this data (sample):
Class A: 6 students; Starts 9/1, ends 12/15
Class B: 5 students; Starts 9/25, ends 11/15
Class D: 10 students; Starts 10/1, ends 10/31
Class E: 8 students; Starts 11/1, ends 12/5
And I'd like the query to give me the following results as a count (or sum?):
Sep.: 11 students (6+5)
Oct.: 15 students (5+10)
Nov.: 19 students (6+5+8)
Dec.: 14 students (6+8)
Probably just a typo. I get the gist, but now I need the specifics. Can you post a more detailed example with table and field names. Use this format for posting:
Ok, you can't create records where there aren't any. A query will only produce rows for what it has data for, it can't figure out that since you are spanning from September - December in one record in the table that it should produce 4 records in the query.
So, you have to create a data source that generates every month you want to report on. Below is that SQL:
Code:
SELECT Month([StartDate]) AS ClassMonth, Year([StartDate]) AS ClassYear
FROM Placements
GROUP BY Month([StartDate]), Year([StartDate])
UNION SELECT Month([EndDate]) AS ClassMonth, Year([EndDate]) AS ClassYear
FROM Placements
GROUP BY Month([EndDate]), Year([EndDate]);
Paste that into a query and save it with the name 'sub_MonthlyStudents_1'. It creates a datasource with all the months* you want to report on. Run it to see what I mean.
Now you've got a datasource with all months you want to report on, now you need a way to compare all your start and end dates with those months to see if the class is in session for that month. The simpliest way to do that is with a function which you pass all the relevant data. This is that code:
Code:
Public Function get_MonthlyStudents(in_Month, in_Year, in_Start, in_End, in_Students)
' if in_Month/in_Year occurs between 1st day of month in_Start is in
' and last day of month in_End is in then returns in_Students, otherwise returns 0
ret = 0
' return value, 0 by default
dt_Start = CDate(Month(in_Start) & "/1/" & Year(in_Start))
' 1st date of month in_Start is in
dt_End = CDate(Month(in_End) & "/1/" & Year(in_End))
dt_End = DateAdd("d", -1, DateAdd("m", 1, dt_End))
' last date of month in_End is in
dt_Test = CDate(in_Month & "/1/" & in_Year)
' creates an actual date out of in_Month and in_Year so can compare to dt_Start and dt_End
If (dt_Test >= dt_Start) And (dt_Test <= dt_End) Then ret = in_Students
' if test date is betwen start and end, then returns in_Students
get_MonthlyStudents = ret
End Function
Paste that into a module and save it. You pass it a month, a year, a class start date, a class end date and the number of students in the class. If the class spans that month/year it returns the total number of students, if not, it returns 0. With that you can add all those values up and see how many students were in each month. This SQL will do that and produce the final results you initially wanted:
Code:
SELECT sub_MonthlyStudents_1.ClassMonth, sub_MonthlyStudents_1.ClassYear, Sum(get_MonthlyStudents([ClassMonth],[ClassYear],[STartDate],[EndDate],[TStudents])) AS Students
FROM Placements, sub_MonthlyStudents_1
GROUP BY sub_MonthlyStudents_1.ClassMonth, sub_MonthlyStudents_1.ClassYear;
Paste that into a query and you have your results. In case you care, open that query up in design view and you will see I use the sub query and Placements in the query, but I don't join them. That's called a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product) and it forces every row in the subquery to go with every row in Placements. I use the function to determine which students were in what months and added them all up.
*This subquery only works if every month you want to report on has a start or end date in Placements. For example, if this is all your start and end date data in your table:
You will not produce results for March, because March isn't present in your data. Hopefully your data is so comprehensive every month has a start date or end date in your table. If not, instead of the sub-query, you will need to build and maintain a table that has all the month/year permutations you want to track.
