Creating subtotals with a WHERE condition.

[Petemush]

Hi,

I currently run a report based on a query which shows details about a number of training courses.

I have these courses grouped by who they're a sourced by. So a few are sourced corporately, another few are sourced externally.

Amongst the details shown is how much the course costs.

I'm trying to display a subtotal of how much each group of courses cost. So all the corporately sourced courses have a certain total and all the externally sourced courses have another.

I thought I could create a control on the report and make the controlsource the following:

SELECT Sum([qryReportCoursesInPeriod].[Total Cost]) AS [SumOfTotalCost]
FROM [qryReportCoursesInPeriod]
WHERE ((([qryReportCoursesInPeriod].[CSource])=[CSource]));

and place the control in the group footer.

But I get a syntax error when I try to open the report and for some reason the following SQL shown.

FIRST([SELECT Sum([qryReportCoursesInPeriod].[Total Cost]) AS [SumOfTotalCost]
FROM [qryReportCoursesInPeriod]
WHERE ((([qryReportCoursesInPeriod].[CSource])=[CSource]));])

I don't know where the FIRST has come from, some automatic process thanks to access I guess, and I can't fix it.

Does anyone know how to get around this problem or perhaps knows another solution to what I'm trying to achieve?

Cheers,

Pete

 

[CosmoKramer]

Pete,

If you already have the report grouped by source, then put this as the control source in your group footer field:

=Sum([Total Cost])

 

[Petemush]

Yeah cheers for that cosmo, when I originally tried that, my test data wasn't very good and I thought that I was getting the same result as the overall total but tried it again with different data and noticed it worked after all.

Cheers anyway,

Pete