Sum in Query

[duckers9]

Hi there

I have a field "TotalProspectiveCredits" which is created by the following statement:

"IIf([StudentModules]![Status]="Registered & Attended" Or [StudentModules]![Status]="Registered & Attended" Or [StudentModules]![Status]="Invited" Or [StudentModules]![Status]="pathway nomination",[Heas!credits],0)".. this puts the number of credits that each individual piece of study is worth.

I can generate a report for each student and by using the sum function in the report design I can see the Total Prospective credits that a student has to date for all studies.

However I want to be able to do this in a query format so that I can find out which students have less than say 100 credits in total..

It is probably straight forward but I don't even know where to start..

Can anyone help

Thanks

 

[Joe4]

Do an Aggregate (Totals) Query to sum up the values by student, and you can use the criteria to only include those students with totals under 100.

Take a look at Access' built-in help files for more on Aggregate (Totals) Queries.

 

[duckers9]

Hi there

Thanks for your speedy reply - have had a look on the help on access

I have inserted a new column in the query design and put in

Expr2: Sum([TotalProspectiveCredits]) and set the total bar to "expression"

The student databaseidno field which is unique to each student is set to "group by" and this is in the first column of the query. There are no "sort by" criteria set in the query at all. All other fields in query are set to "group by"

When I run report the data in the Exp2 column either mirrors whats in column "TotalProspectiveCredits" or gives an erroneous value - for instance 1 students records has values in the "Exp2" column from 20 - 90 credits, but they don't seem to follow any logical pattern ie 20+15 =35...

Any help would be appeciated...

I have done quite a few queries on this database, but this one is beyond me...

Thanks in anticipation

 

[SRienstra]

Your second post is confusing. From your description, Expr2 should only differ from TotalProspectiveCredits when there is duplication of some sort inthe underlying data, and then Expr2 should always be a multiple of TotalProspectiveCredits.

 

[Joe4]

Yes, your last post is a bit confusing. It is hard to visualize what is going on without being able to see the data you are working with.

Can you post a small sample of your data, and then what your expected results would look like?

 

[james_lankford]

select 
  [student databaseidno], 
  sum ( [Heas!credits] ) 
from 
  ?? 
where 
(
  [StudentModules]![Status] = "Registered & Attended" 
  Or 
  [StudentModules]![Status] = "Registered & Attended" 
  Or 
  [StudentModules]![Status] = "Invited" 
  Or 
  [StudentModules]![Status] = "pathway nomination"
)
group by 
  [student databaseidno]

I'm guessing you only need the group by on the student id
so something like the above

 

[duckers9]

Sorry I didn't mean to make it sound confusing ... I'll try to explain a bit more...

Here goes (deep breath)

I have tables -
Modules - where the module number, title and credits are kept
Students - where the student details are kept
student modules - were the attendance details are kept.

I have designed a report which shows the modules the student is planning to take or has taken, the credits they would be worth if they are all attended and the total credits following attendance...

worth attended prospective actual
Student module credits status credits credits

john attended module A which is worth 10 credits
he then attended module B which is worth 15 credits

This gives him a prospective credit score of 25 and an actual credit score of 25 (cos he attended)

He then plans to attend module C which is worth 40 credits - this would give him a prospective credit score of 65. However when he does not attend his total actual credit score would still be 25.



the report works fine, but I would like to use a query to pick out the students who have a total actual credits less than a certain value.

I am hoping then do change the query slightly to look for students with a total prospective credits less than a certain value.

Does this help at all?

 

[Joe4]

Did you try what James recommended?

To do an Aggregate Query, all you need to do is add the field(s) you want to group by (Student ID), and then the field you want to sum (and no other fields!).

Then click on the Totals button (looks like a Sigma). This will add a Totals Row under the fields you have selected with the words "Group By" under each. Under the fields you want to sum by id, change the words "Group By" to "Sum".

 

[SRienstra]

If you're using SQL view to enter your query, add a line after the group by:
Having sum([relevant field])<100

If you're in design view, just add <100 (or whatever number you decide upon) to the criteria for the field in which you're summing what you want to use as the basis for selection.