Sum in Group footer

[tucker61]

I have a report that brings back the top ten items by cost value,
This is on the report detail, as a cancel event peice of code.

On the same report, i want to sum up the cost price in the footer, but when i sum the control '=sum([Cost]) in the group footer, it seems to sum the whole of the items in the cost column, and not just the top 10.

But when i export the report it to excel, it seems to be adding up correctly.

Enclosed snapshots show report in access, including the sum in the footer, and the report in excel which is the correct figure.

What have i done wrong ?

 

[PNGBill]

When you

=Sum([myfield])

you are summing the fields in your Record Source. Not the controls on your form.

If your report is using a query/sql as it's records source, ensure it only returns the top 10 records and the Sum should work.

 

[tucker61]

Thanks, I suppose the issue is then with the query.

I have changed my query so it brings back the top 10 in descending order by cost.

Problem is that i have 4 product types, and need to show the top 10 for all product types.

Here is my sql code in my query,

SELECT TOP 10 [SumOfQty]*[price] AS [cost], [Type].[Product]
FROM (Data_Qry LEFT JOIN DME ON Data_Qry.Cat = DME.Cat) LEFT JOIN [type] ON Data_Qry.Cat = [type].NUMBER
GROUP BY DME.Price, [SumOfQty]*[price], [type].[product]
ORDER BY [SumOfQty]*[price] DESC;

 

[PNGBill]

Do you have a table named Type ? If so, type is a reserved word.

Also, Number is a reserved word.

 

[PNGBill]

Problem is that i have 4 product types, and need to show the top 10 for all product types.
You want 4 Top 10 results ?

On the original issue, if your Report sql has all the records returned, the report should should be able to Sum on just the records filtered but maybe you have to set your footers to reflect what you sum.

eg, we have a report for annual sales. We have sum for month and sum for year.
Month sum is in the Month footer and year sum is in the report footer - final footer.

Reports have a good Grouping setup.

 

[tucker61]

Problem is that i have 4 product types, and need to show the top 10 for all product types.
You want 4 Top 10 results ?

On the original issue, if your Report sql has all the records returned, the report should should be able to Sum on just the records filtered but maybe you have to set your footers to reflect what you sum.

eg, we have a report for annual sales. We have sum for month and sum for year.
Month sum is in the Month footer and year sum is in the report footer - final footer.

Reports have a good Grouping setup.

That is exactly the issue.
I have thought about having 4 queries, but then if the number of product types increases I would need to build more.

I have now changed the code to get rid of the reserved. Ames, and still have same issue.

I think the problem is trying to sum the calculated control within the group footer.

Should i try to do the calculation in the vba?

 

[PNGBill]

Use your Report Grouping facility.
Group on producttype and use

=Sum([myfield])

in each of the group footers.
4 product types = 4 groups and respective footers.

 

[tucker61]

Already tried, but as per screen shots above.

I have changed the query to bring back all the data, so I can group on the report.
Now on my report, i have the data ranked, by cost price, with grouping by product type.
when the counter gets to >10 a cancel event cancels the remaining data from being shown on the report.
When the report runs the sum in the footer seems to sum up all the data in that group, not just the top10 that are showing on the report.

 

[PNGBill]

Looks like what ever event restricts teh records to 10 in the report does not reduce the records in the recordset which then appears to mean the =Sum() sees additional records to what the report displays.

If it is allways 10 records, then maybeyou should do the filtering in the report record source.

It may require a Union Query or a Subquery to get the top 10 records of each group in the one record source for your report.

If it is not allways 10, then you could use a variable in teh record source query for the number of records to return.