Access Report & Running Sum

  • Thread starter Thread starter Shock
  • Start date Start date
S

Shock

Hi everyone,

I have never posted to this group before, but I am desperate! I am
helping a friend with an access database and I am having trouble with
the running sum option. Here is the detail line and some fictional
data below:

Course Grade Points CumPts

Cis440 A 4 4
CSC235 B 3 7
mgt590 D 1 8

Right now I am only able to get the first three columns to work
correct. I used a query to get the information in those columns and
then I brought that information into the report. Now, inside the
report I am trying to get the cumpts column to work correctly. So far
I have created a textbox for that column in the detail section and I
have changed control source to =sum(Points) and runningsum to over
group. I thought this would work since runningsum increments the
field by the field in parentheses in the last sentence (i.e.
=sum(Points). But this isn't working for me. Instead I get this
result:

Course Grade Points CumPts

Cis440 A 4 4
CSC235 B 3 3
mgt590 D 1 1

which is obviously incorrect. I am sorry to bother with such a simple
problem for most of you, but it is 3:30 am and I am pretty frustrated.
I thought this might be a grouping problem, but I don't see how
because the group header for this detail section contains
StudentNumber which is used to group the students classes/grades by.
Any help is appreciated!

Thanks,

Shock
 
Set these properties for your CumPts text box:
Control Soure Points
Running Sum Over Group

It does not need an =Sum(...
This assmes that Points is a field of type Number.
 
Allen,

Thanks for the response, however, I made a mistake on my initial post.
I have the control source set to Points (not =sum(Points)) and I have
running sum set to over group and I am still getting the same results.


Course Grade Points CumPts

Cis440 A 4 4
CSC235 B 3 3
mgt590 D 1 1

Shock56
 
Are these in the Detail Section?

If there are no other sections, you could change the RunningSum property to
Over All
 
Sorry, I should have sent you a section list. This is what I have

Report Header - title of report
Page Header - headers for columns
StudentNumber Header (group)
Detail - course, grade, points, cumpts
Page Footer - page stuff

Shock56
 
Okay, so you have a StudentNumber group header, and CumPts is in the detail
section.

Setting the Running Sum property of CumPts to:
Over Group
should accumulate the totals for the student.
 
Yep, but can't get it to work. Seems weird I know, but something is a miss.
I can upload the file somewhere if you think you would have time to look at
it to help me find the bug. Any help appreciated.
shock
 
Your query uses a calculated field for Points:
Points: IIf([Grade]="A", "4", IIf([Grade]="B", "3", ...
Because you used quote marks around the values, Access thinks it is text,
and it cannot perform a running sum on the text.

Try:
Points: IIf([Grade]="A", 4, IIf([Grade]="B", 3, ...

BTW, the Switch() function might be better than the embedded immediate Ifs.
 
Allen,

Thanks a ton! I program c++ so it is hard for me to think I would do
something like this, but stuff happens. Thanks again, Allen!

Corey
Allen Browne said:
Your query uses a calculated field for Points:
Points: IIf([Grade]="A", "4", IIf([Grade]="B", "3", ...
Because you used quote marks around the values, Access thinks it is text,
and it cannot perform a running sum on the text.

Try:
Points: IIf([Grade]="A", 4, IIf([Grade]="B", 3, ...

BTW, the Switch() function might be better than the embedded immediate Ifs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Corey Perkins said:
Yep, but can't get it to work. Seems weird I know, but something is a miss.
I can upload the file somewhere if you think you would have time to look at
it to help me find the bug. Any help appreciated.
shock
I
have
Click to expand...
desperate!
I inside
the correctly.
So section
and to
over increments
the
Click to expand...
such
a
Click to expand...
Click to expand...
 
Great.

Corey, if you are more familiar with C++, the VBA types probably look quite
sloppy to you. They can be, but I find it really useful to type as tightly
as possible, and explictly typecast when intended.

Some general tips
1. For unbound text boxes, set the Format property to General Number or
Short Date or whatever, as a way of specifying the type.

2. In calculated query fields, wrap the calculation in CLng(), CDbl(),
CDate(), etc.

3. Bear in mind that only the Variant can be Null, so use Nz() as well
before one of the typcasting functions. For a calculated field in a query,
it would look like this:
Amount: CCur(Nz([Quantity], 1) * Nz([UnitPrice], 0))

4. Always declare any parameters in a query (Paramenters on Query menu in
query design view), so you can specify the data type.

Further tips:
http://allenbrowne.com/ser-30.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Corey Perkins said:
Allen,

Thanks a ton! I program c++ so it is hard for me to think I would do
something like this, but stuff happens. Thanks again, Allen!

Corey
Allen Browne said:
Your query uses a calculated field for Points:
Points: IIf([Grade]="A", "4", IIf([Grade]="B", "3", ...
Because you used quote marks around the values, Access thinks it is text,
and it cannot perform a running sum on the text.

Try:
Points: IIf([Grade]="A", 4, IIf([Grade]="B", 3, ...

BTW, the Switch() function might be better than the embedded immediate Ifs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Corey Perkins said:
Yep, but can't get it to work. Seems weird I know, but something is a miss.
I can upload the file somewhere if you think you would have time to
Click to expand...
look
at
it to help me find the bug. Any help appreciated.
shock
Okay, so you have a StudentNumber group header, and CumPts is in the
detail
section.

Setting the Running Sum property of CumPts to:
Over Group
should accumulate the totals for the student.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Sorry, I should have sent you a section list. This is what I have

Report Header - title of report
Page Header - headers for columns
StudentNumber Header (group)
Detail - course, grade, points, cumpts
Page Footer - page stuff

Shock56
Are these in the Detail Section?

If there are no other sections, you could change the RunningSum
property
to
Over All

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the response, however, I made a mistake on my initial
post.
I have the control source set to Points (not =sum(Points))
Click to expand...
Click to expand...
and
I desperate! such see
how
Click to expand...
Click to expand...
 
Yeah, I was thinking that things were a little sloppy in VBA, especially
that nested iif that i wrote. :).

Thanks again,

Corey

Allen Browne said:
Great.

Corey, if you are more familiar with C++, the VBA types probably look quite
sloppy to you. They can be, but I find it really useful to type as tightly
as possible, and explictly typecast when intended.

Some general tips
1. For unbound text boxes, set the Format property to General Number or
Short Date or whatever, as a way of specifying the type.

2. In calculated query fields, wrap the calculation in CLng(), CDbl(),
CDate(), etc.

3. Bear in mind that only the Variant can be Null, so use Nz() as well
before one of the typcasting functions. For a calculated field in a query,
it would look like this:
Amount: CCur(Nz([Quantity], 1) * Nz([UnitPrice], 0))

4. Always declare any parameters in a query (Paramenters on Query menu in
query design view), so you can specify the data type.

Further tips:
http://allenbrowne.com/ser-30.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Corey Perkins said:
Allen,

Thanks a ton! I program c++ so it is hard for me to think I would do
something like this, but stuff happens. Thanks again, Allen!

Corey
Allen Browne said:
Your query uses a calculated field for Points:
Points: IIf([Grade]="A", "4", IIf([Grade]="B", "3", ...
Because you used quote marks around the values, Access thinks it is text,
and it cannot perform a running sum on the text.

Try:
Points: IIf([Grade]="A", 4, IIf([Grade]="B", 3, ...

BTW, the Switch() function might be better than the embedded immediate Ifs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yep, but can't get it to work. Seems weird I know, but something is a
miss.
I can upload the file somewhere if you think you would have time to look
at
it to help me find the bug. Any help appreciated.
shock
Okay, so you have a StudentNumber group header, and CumPts is in the
detail
section.

Setting the Running Sum property of CumPts to:
Over Group
should accumulate the totals for the student.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Sorry, I should have sent you a section list. This is what I have

Report Header - title of report
Page Header - headers for columns
StudentNumber Header (group)
Detail - course, grade, points, cumpts
Page Footer - page stuff

Shock56
Are these in the Detail Section?

If there are no other sections, you could change the RunningSum
property
to
Over All

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the response, however, I made a mistake on my initial
post.
I have the control source set to Points (not =sum(Points))
Click to expand...
and
I
have
running sum set to over group and I am still getting the same
results.


Course Grade Points CumPts

Cis440 A 4 4
CSC235 B 3 3
mgt590 D 1 1

Shock56


Set these properties for your CumPts text box:
Control Soure Points
Running Sum Over Group

It does not need an =Sum(...
This assmes that Points is a field of type Number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Hi everyone,

I have never posted to this group before, but I am desperate!
I
am
helping a friend with an access database and I am having
trouble
with
the running sum option. Here is the detail line and some
fictional
data below:

Course Grade Points CumPts

Cis440 A 4 4
CSC235 B 3 7
mgt590 D 1 8

Right now I am only able to get the first three columns to
work
correct. I used a query to get the information in those
columns
and
then I brought that information into the report. Now, inside
the
report I am trying to get the cumpts column to work correctly.
So
far
I have created a textbox for that column in the detail section
and
I
have changed control source to =sum(Points) and
Click to expand...
runningsum
to
over
group. I thought this would work since runningsum increments
the
field by the field in parentheses in the last sentence (i.e.
=sum(Points). But this isn't working for me. Instead I get
this
result:

Course Grade Points CumPts

Cis440 A 4 4
CSC235 B 3 3
mgt590 D 1 1

which is obviously incorrect. I am sorry to bother with such
a
simple
problem for most of you, but it is 3:30 am and I am pretty
frustrated.
I thought this might be a grouping problem, but I don't see
how
because the group header for this detail section contains
StudentNumber which is used to group the students
classes/grades
by.
Any help is appreciated!

Thanks,

Shock
Click to expand...
Click to expand...
Click to expand...
 
Back
Top