Filter a record on sum of a field

[mshakeelattari]

I have a query that gets data from different tables:

StudentsExams.studentID| StudentsExams.PaperID | Papers.MaxMarks | StudentsExams.ObtdMarks

Then I create a report on this query grouped on StudentID and the Detail contains:

PaperID | MaxMarks | ObtdMarks | %age (Obtd*100/Max)

The StudentID Footer contains:

Total: Sum(MaxMarks) | Sum(ObtdMarks) | %age(Sum(ObtdMarks)*100/sum(MaxMarks))

Now I want only those studentID's records where [sum(obtdmarks)*100/sum(maxmarks)>33%.

And I not sure how to do this? Please help me. Thankin in advance!

[twinnyfo]

Although it would be slow, you could use a DSum() Function, in which you use the Criteria you specify. If you have a lot of records, it would be slow.

You could also create a separate report that lists only the students with marks above 33% (criteria in your query), but then you have two reports and I think you want to be able to do this in one (or--you want this report to show only those with >33%)?

It is also difficult to understand what your report looks like, because are you looking for the student's average scores or only those scores >33%. When you are summing values like this, wouldn't you want to average the scores or is it a cumulative score?

Lot's of questions, I know, but your end result is unclear.

[mshakeelattari]

First of all thanks for your early response. Here is a report
http://s17.postimg.org/cfx0l3ncv/Untitled.png
http://s25.postimg.org/k383kimrj/Untitled.png

On the studentID footer, I get sum of ObtdMarks and sum of MaxMarks and then get the percentage of the ObtdMarks. Now I want those students whose percentage of the total is >33%.

[twinnyfo]

Again, the only way I know how to do that would be using the DSum() Function which would check the status of each student. Then apply that filter to the report. It's not pretty, but when you are trying to apply a filter to an aggregated value on a Header/Footer, it's just about the only way (that I know of).

[jforbes]

It looks to me that your calculations and summations are being done by the Report. This works pretty well up until you do something like you are attempting to do, Filter the report based on a summation of a calculated column. Twinnyfo is correct, that you will need to do all these calculations beforehand in the report and then filter on them, if you want to keep all these calculations in the Report.

The other option is to pull all the calculations and summations out of the Report and put them into Queries. To do this

1. Make a Select Query called something like PaperTotals, based on StudentsExams, link the Papers table, create the calculated columns per Paper (Grade).
2. Make an Aggregate Query called SubjectTotals, based on PaperTotals and Grouped By SubjectTitle.
3. Make an Aggregate Query called StudentTotals based on PaperTotals that Groups By Students and Sums up their totals. This is where the Students Overall Grade would be calculated; the one you are looking to filter by.
4. Make a third Query called StudentInfo that links Students table to the Student Totals, that way all the Student Info is available when you create the Main Report.
5. Create a SubReport that is based on SubjectTotals. Order it by SubjectTitle, if you want.
6. Create the MainReport that is based on StudentInfo. You won't need to create headers or footers, unless you want an overall summation on your Students. Then drop in the SubReport so you can see your Students detail by Subject.

Access' Report builder is great for most things, but if attempt to get at top level calculations, you will have trouble.

[mshakeelattari]

where should I use DSum? in query? please

[twinnyfo]

You would have a new field in your query in which you use the DSum function based on the values of your r student fields. You would get a value for each record, which should be the same for each student, as you are calculating their total average score. Your query field will look something like this:

DSum("[ObtdMarks]", "StudentExams", "StudentID = " & [StudentID])/DSum("[MaxMarks]", "StudentExams", "StudentID = " & [StudentID]) AS StudentAvgMarks

This assumes student ID is a numeric value.