Sbovino
asked on
Ms Access Query - Can this query be redesigned to provide summary data only
The query below produces enrollment data for every grade in the school for a given year. I would like to create a query that provides just a total enrollment number per school for the given year. The current query when parameters are applied produces the following as an example:
School A for a certain year
Grade 6 100
Grade 7 100
Grade 8 100
I have a report that sums the totals and compares them. I would like a query that does the summing for use in other reports. For example when the year parameters are applied I just want the total as below.
School A 300
My current sql follows
School A for a certain year
Grade 6 100
Grade 7 100
Grade 8 100
I have a report that sums the totals and compares them. I would like a query that does the summing for use in other reports. For example when the year parameters are applied I just want the total as below.
School A 300
My current sql follows
SELECT enrollment.er_school_id, Sum(IIf([Year]=forms!select_dates!current_year,[enroll_data],0)) AS [Current Enrollment], Sum(IIf([Year]=forms!select_dates!proposed_year,[enroll_data],0)) AS [Proposed Enrollment], ([Proposed Enrollment]-[Current Enrollment]) AS [+/- Enrollment], enrollment.enroll_desc, enrollment.erdisplay_order, schools.schoollName, schools.school_id, Enroll_gradeNames.dgrdisplay_order, schools.Type
FROM schools INNER JOIN (enrollment LEFT JOIN Enroll_gradeNames ON enrollment.enroll_desc = Enroll_gradeNames.Grade_name) ON schools.school_id = enrollment.er_school_id
WHERE (((schools.open_date)<="forms]![select_dates]![current_year]") AND ((schools.close_date)>=[forms]![select_dates]![proposed_year]))
GROUP BY enrollment.er_school_id, enrollment.enroll_desc, enrollment.erdisplay_order, schools.schoollName, schools.school_id, Enroll_gradeNames.dgrdisplay_order, schools.Type;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER