How do I count individual records in the Detailed section of a Rep

R

ran

Hi: I’m working on an Access report. I have a table that has records that
list:
Year, Term, Class, Department, Section and each grade given in each class
section. My report should summarize each class section. This summary should
have: Number of students in the class. Here I use Count(*) which gives me
the correct number of students in the class.

But I’m also trying to get the number of As, Bs, Cs Ds & Fs in each
individual class section. These are coded as 4,3,2,1, & 0 respectively. I
am able to get a class section average, the minimum grade and the maximum
grade along with a standard deviation for each class section. However I
cannot seem to get a count of how many 4(As) or 3(Bs) and so forth for each
class section.. Thank you (I’m using Access 2000)
 
D

Duane Hookom

Do you mind sharing your table structure? Specifically, I'm wondering if
classes are fields or is your table structure normalized?
 
R

ran

My table has these fields: Dept (text), Year (Date), Term (text), Session
(text), GPA (byte, can be 0 to 4), Class (text), Section (text), Class
(text), Title (text).

Please let me explain in more detail. Each Department has a list of classes
that they offer at different sessions (Fall, Spring, Summer) during the year.
These sections have students that are given grades. Here the field is named
GPA, since it is the grade point average for the student for that
class/section. For instance Acc220 002. There may be a Acc220 002 in the
Fall, Spring and Summer of each year.

Each class/section has X amount of students. That I can count using
Count(*).

My dilemma is how to count the number of 0, 1, 2, 3, and 4s (the grades:
0=F, 1=D, 2=C, 3=B, 4=A) that may be distributed to the students in the GPA
column. For instance Acc220 002 may have 12 records (students). Of these 12
records how many are 0, 1, 2, 3, and 4s? In other words how many As Bs Cs
and so forth were given in a particular class section.
 
D

Duane Hookom

If I understand correctly, you need to create a subreport based on a totals
query that groups by GPA and others such as Class etc. Count the number of
GPA in the totals query. Add the subreport to a footer in your main report
and join the appropriate fields.
 
R

ran

By sub report do you mean detailed summary? I need to summarize a class’
attributes. By class I mean Class/Section for instance Acc220 002. By
attributes I mean Class average GPA, class minimum GPA, class maximum GPA,
the standard deviation within the class, number of students in the class and
the number of As, Bs, Cs, Ds and Fs. These are represented by numbers: 4, 3,
2, 1 and 0 respectively.

I am able to summarize most of the attributes I am looking for in the Detail
footer by the following: =AVG(GPA), = Min(GPA), =Max(GPA), = StDev(GPA) and
=Count(GPA). This portion works nicely. But how do I breakdown the count to
a count of 4, 3, 2, 1 and 0s?

I can list each GPA for the Class/Section in the Detail section of the
report. However this is not what I want. I do not want a list but rather a
summary count. I’m not sure but I think the answer in VBA code. I just
don’t know how to write it. Something along the lines of count the
particular Class/Section GPA records that = 4 and then count the GPA records
that = 3 and so forth. Thanks for your help and patience. It is
appreciated.
 
D

Duane Hookom

You need to create a totals query that counts GPA grouped by GPA,
Class/Section and whatever other field that your report might be Grouped by.
This should result in a record set of your number of students with As, Bs,
....or 4s, 3s, 2s,...

Use this totals query as the Record Source of a report. Then add this report
to the Class/Section footer or header of your report and set the Link
Master/Child properties to Class/Section and whatever other field that your
report might be Grouped by.
 
R

ran

Sorry for the delay in getting back, network connectivity issues.
I’m not sure I have the expertise to do what you are recommending. I
created a query that counts the number of 4s in a particular class/section.
How do I associate this query with the Record Source? Or do I? When I click
on Text Box in Design View for Control Source I choose the field, in this
case GPA. Then paste my query into the Expression Builder. However I feel
that this is an improper method in that 1, the results are inaccurate.
Everything is 4. And 2, when I go back to check the query in Expression
Builder after previewing the form, all I see is the field name (GPA). Here
is the query I paste.

SELECT DISTINCTROW [GPAsections2005].[Class], [GPAsections2005].[Section],
Count(*) AS [Count Of GPAsections2005]
FROM GPAsections2005
WHERE ((([GPAsections2005].[GPA])=4))
GROUP BY [GPAsections2005].[Class], [GPAsections2005].[Section];
 
D

Duane Hookom

Create your totals query as
SELECT [Class], [Section],[GPA], Count(*) AS [NumOf]
FROM GPAsections2005
GROUP BY [Class], [Section],[GPA];

Then create a report based on the above query. Add this new report as a
subreport into the [Section] Footer of your main report. Set the Link
Master/Child property to
[Class],[Section]

--
Duane Hookom
MS Access MVP


ran said:
Sorry for the delay in getting back, network connectivity issues.
I'm not sure I have the expertise to do what you are recommending. I
created a query that counts the number of 4s in a particular
class/section.
How do I associate this query with the Record Source? Or do I? When I
click
on Text Box in Design View for Control Source I choose the field, in this
case GPA. Then paste my query into the Expression Builder. However I
feel
that this is an improper method in that 1, the results are inaccurate.
Everything is 4. And 2, when I go back to check the query in Expression
Builder after previewing the form, all I see is the field name (GPA).
Here
is the query I paste.

SELECT DISTINCTROW [GPAsections2005].[Class], [GPAsections2005].[Section],
Count(*) AS [Count Of GPAsections2005]
FROM GPAsections2005
WHERE ((([GPAsections2005].[GPA])=4))
GROUP BY [GPAsections2005].[Class], [GPAsections2005].[Section];



Duane Hookom said:
You need to create a totals query that counts GPA grouped by GPA,
Class/Section and whatever other field that your report might be Grouped
by.
This should result in a record set of your number of students with As,
Bs,
....or 4s, 3s, 2s,...

Use this totals query as the Record Source of a report. Then add this
report
to the Class/Section footer or header of your report and set the Link
Master/Child properties to Class/Section and whatever other field that
your
report might be Grouped by.
 
R

ran

Sorry for the delay in getting back, bad/terrible network connectivity.
I tried using a sub report. But that didn’t really do the trick. However
after looking at your query I realized my original query was not all together
correct. I modified my original query. Then I joined your count query with
my modified query. This puts a count of al records in a particular
class/sections on each record. Each record contains a grade type (0-4) a
count of the particular grade type and a count of how many records associated
with the class/record. This allows me to group the by class, so I get a
detailed summary of classes per Term.

Thank you so much! I could not have done this without your help.


Duane Hookom said:
Create your totals query as
SELECT [Class], [Section],[GPA], Count(*) AS [NumOf]
FROM GPAsections2005
GROUP BY [Class], [Section],[GPA];

Then create a report based on the above query. Add this new report as a
subreport into the [Section] Footer of your main report. Set the Link
Master/Child property to
[Class],[Section]

--
Duane Hookom
MS Access MVP


ran said:
Sorry for the delay in getting back, network connectivity issues.
I'm not sure I have the expertise to do what you are recommending. I
created a query that counts the number of 4s in a particular
class/section.
How do I associate this query with the Record Source? Or do I? When I
click
on Text Box in Design View for Control Source I choose the field, in this
case GPA. Then paste my query into the Expression Builder. However I
feel
that this is an improper method in that 1, the results are inaccurate.
Everything is 4. And 2, when I go back to check the query in Expression
Builder after previewing the form, all I see is the field name (GPA).
Here
is the query I paste.

SELECT DISTINCTROW [GPAsections2005].[Class], [GPAsections2005].[Section],
Count(*) AS [Count Of GPAsections2005]
FROM GPAsections2005
WHERE ((([GPAsections2005].[GPA])=4))
GROUP BY [GPAsections2005].[Class], [GPAsections2005].[Section];



Duane Hookom said:
You need to create a totals query that counts GPA grouped by GPA,
Class/Section and whatever other field that your report might be Grouped
by.
This should result in a record set of your number of students with As,
Bs,
....or 4s, 3s, 2s,...

Use this totals query as the Record Source of a report. Then add this
report
to the Class/Section footer or header of your report and set the Link
Master/Child properties to Class/Section and whatever other field that
your
report might be Grouped by.


--
Duane Hookom
MS Access MVP

By sub report do you mean detailed summary? I need to summarize a
class'
attributes. By class I mean Class/Section for instance Acc220 002. By
attributes I mean Class average GPA, class minimum GPA, class maximum
GPA,
the standard deviation within the class, number of students in the
class
and
the number of As, Bs, Cs, Ds and Fs. These are represented by numbers:
4,
3,
2, 1 and 0 respectively.

I am able to summarize most of the attributes I am looking for in the
Detail
footer by the following: =AVG(GPA), = Min(GPA), =Max(GPA), = StDev(GPA)
and
=Count(GPA). This portion works nicely. But how do I breakdown the
count
to
a count of 4, 3, 2, 1 and 0s?

I can list each GPA for the Class/Section in the Detail section of the
report. However this is not what I want. I do not want a list but
rather
a
summary count. I'm not sure but I think the answer in VBA code. I
just
don't know how to write it. Something along the lines of count the
particular Class/Section GPA records that = 4 and then count the GPA
records
that = 3 and so forth. Thanks for your help and patience. It is
appreciated.

:

If I understand correctly, you need to create a subreport based on a
totals
query that groups by GPA and others such as Class etc. Count the
number
of
GPA in the totals query. Add the subreport to a footer in your main
report
and join the appropriate fields.

--
Duane Hookom
MS Access MVP

My table has these fields: Dept (text), Year (Date), Term (text),
Session
(text), GPA (byte, can be 0 to 4), Class (text), Section (text),
Class
(text), Title (text).

Please let me explain in more detail. Each Department has a list of
classes
that they offer at different sessions (Fall, Spring, Summer) during
the
year.
These sections have students that are given grades. Here the field
is
named
GPA, since it is the grade point average for the student for that
class/section. For instance Acc220 002. There may be a Acc220 002
in
the
Fall, Spring and Summer of each year.

Each class/section has X amount of students. That I can count using
Count(*).

My dilemma is how to count the number of 0, 1, 2, 3, and 4s (the
grades:
0=F, 1=D, 2=C, 3=B, 4=A) that may be distributed to the students in
the
GPA
column. For instance Acc220 002 may have 12 records (students). Of
these
12
records how many are 0, 1, 2, 3, and 4s? In other words how many As
Bs
Cs
and so forth were given in a particular class section.


:

Do you mind sharing your table structure? Specifically, I'm
wondering
if
classes are fields or is your table structure normalized?


--
Duane Hookom
MS Access MVP

Hi: I'm working on an Access report. I have a table that has
records
that
list:
Year, Term, Class, Department, Section and each grade given in
each
class
section. My report should summarize each class section. This
summary
should
have: Number of students in the class. Here I use Count(*) which
gives
me
the correct number of students in the class.

But I'm also trying to get the number of As, Bs, Cs Ds & Fs in
each
individual class section. These are coded as 4,3,2,1, & 0
respectively.
I
am able to get a class section average, the minimum grade and the
maximum
grade along with a standard deviation for each class section.
However
I
cannot seem to get a count of how many 4(As) or 3(Bs) and so
forth
for
each
class section.. Thank you (I'm using Access 2000)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top