I have created an InfoPath form that allows users to submit jobs to our Reprographics department and it tallies up the cost of their job so it can be billed to their department at the end of the month.
Each department (faculty) has multiple sub faculties.
The query I use to get the monthly results is:
SELECT FacultyName, SubFacultyName, Totalprice
From Jobs
Where Year(DateSubmitted) = Year(CURRENT_TIMESTAMP)
AND Month(DateSubmitted) = Month(CURRENT_TIMESTAMP)
Order by FacultyName, SubFacultyName
This produces three columns. One showing the faculty, the second showing the subfaculty and the third showing the cost. There is a row for each job that has been submitted.
Something like this:
Facultyname Subfacultyname Totalprice
Dev Dev1 0.01
Dev Dev1 0.23
Dev Dev2 1.32
Dev Dev3 2.11
Research Res1 0.12
Research Res1 0.02
Can anyone guide me (I am a complete beginner!) with regard to adding subtotals in? Ideally I'd like a Subtotal on a separate line each time the SubFaculty changes in the results. So in the example above I'd have subtotals for Dev1, Dev2, Dev3 and Res1.
Thanks.
Each department (faculty) has multiple sub faculties.
The query I use to get the monthly results is:
SELECT FacultyName, SubFacultyName, Totalprice
From Jobs
Where Year(DateSubmitted) = Year(CURRENT_TIMESTAMP)
AND Month(DateSubmitted) = Month(CURRENT_TIMESTAMP)
Order by FacultyName, SubFacultyName
This produces three columns. One showing the faculty, the second showing the subfaculty and the third showing the cost. There is a row for each job that has been submitted.
Something like this:
Facultyname Subfacultyname Totalprice
Dev Dev1 0.01
Dev Dev1 0.23
Dev Dev2 1.32
Dev Dev3 2.11
Research Res1 0.12
Research Res1 0.02
Can anyone guide me (I am a complete beginner!) with regard to adding subtotals in? Ideally I'd like a Subtotal on a separate line each time the SubFaculty changes in the results. So in the example above I'd have subtotals for Dev1, Dev2, Dev3 and Res1.
Thanks.