Sum in Query

duckers9

New Member
Joined
Jan 19, 2008
Messages
8
:)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..:confused:

Can anyone help :)

Thanks
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 19, 2008
Messages
8
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...:confused:

Any help would be appeciated...:)

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

Thanks in anticipation
 

SRienstra

New Member
Joined
Dec 3, 2009
Messages
14
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 11, 2009
Messages
1,178
Code:
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

New Member
Joined
Jan 19, 2008
Messages
8

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 3, 2009
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top