create report grouped by various criteria

[Guest]

I am trying to build two types of transcripts based on a query that I have.
The first one I did successfully. It groups the data by students, then by
year and semester. This way, you can see all the courses a student took
every semester.

I am stuck on making the second type of transcript from the same query. For
this query, I need to make subgroups of course types so that, for example,
all math courses appear together, all english courses, etc.

The fields that I have are StudentID, Last, First, Year, Semester,
CourseCode, CourseTitle, Grade.

The CourseCode 's come in many forms, but math courses begin with "M" so I
know that I can group all courses with the criteria "M*". Beyond that, I am
stuck. Please help.

Thank you.

 

[Al Campagna]

vanguardaccess,
You didn't give us all the info on the new report, just that you wanted
to group by CourseCode.
Using the logic of your first report, you should be able to group by...
Year
Semester
CourseCode
Last
First
If you're having problems figuring out how to "group" on CourseCode,
we'll have to see what you have for CourseCode data. What are typical
CourseCode values, and what was the logic in your design of that field?

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[Guest]

Hi vanguard,

You something to separate the courses. Why not add a 'discipline' field to
your courses table?

CW

 

[Guest]

Thanks for the response. There are many different CourseCodes, which is part
of the problem. The logic behind them is complicated -- it is all imported
from the city's database. For the city system all the first letters in the
codes determine the departments they are in (eg. M=Math). However, different
schools have different requirements, so the city database doesn't correspond
with my school's requirements. For us, all of the CourseCodes that begin
with EH and followed by a number are english courses, those beginning with E
and followed by a number are reading/writing courses, and belong in a
different group. Those beginning with ME, MB, or MG and followed by a number
are math courses. Those beginning with MQ and followed by a number are art
courses. And so on. There are about 100 different CourseCodes, and somehow
I need to tell the report to put some in an english group, some in a math
group, some in an art group, etc. (Could I perhaps add a column in the query
that defines the department with some sort of conditional statement? If so
-- how?)

Here are example data from the Query:
ID - Name - Year - Semester - CourseCode - CourseTitle - Grade.
01 - John - 2005 - 1 - EH2 - English - 77
01 - John - 2005 - 2 - EH3 - English - 81
01 - John - 2005 - 1 - ME31 - Geometry - 93
02 - Ari - 2005 - 1 - EH1 - English - 65
02 - Ari - 2005 - 1 - ME33 - Geometry - 78
02 - Ari - 2005 - 2 - MB31 - Algebra - 68

Given these data, the report should make one page for John and one for Ari.
Within John's page, it should take the two english courses and put them in
one group, then the geometry course and put it in another. For Ari's page,
it should put the english course (EH1) in one group and both the math courses
(ME33 & MB31) in another.

Many thanks.

--vanguardaccessman

 

[Guest]

That makes sense. I know that in the Field row of the query I can create
conditional expressions, but how would I do this if I need multiple
conditions?

For example:

CourseCodes = E1, E2, EH1, EH2, ME31, ME41, MB43

Given those course codes, I want the "disciplines" column of my query to say
English for the first two, Humanities for the second two, and math for the
third three.

Can I use "IIF"? I know how to do this for one condition, but not for many.

Thanks!

 

[Pieter Wijnen]

IIF(Mid(CourseCodes,2,1)="H","Humanities",IIF(Left(CourseCodes,1)="M","Math","English"))

HtH

Pieter

 

[Guest]

This is great. I perhaps should have clarified, however. I have about 100
different codes and was just giving a simplified example. For every
different code, I will need to specify what department to put it to. In some
cases, every thing that starts with a letter (e.g. G) will belong to one
discipline -- whether GB1, GB3, or GM3. In other cases, such as with codes
beginning in E, I will need to be more specific. Those beginning with E and
followed by a number (eg. E1 or E3) will be in the Reading Discipline, and
those beginning with EH and followed by a number will belong to the English
discipilne. Can you help me find the right IIF so that I can customize it
for the codes that I need? Can you possible explain what each part of the
expression that you are including means? Thanks, that helps A LOT!

 

[Pieter Wijnen]

Then you should rather use a Lookup table for the relations & Simply use
DLookup (or Table Join)
ie
=DLookup("Discipline","Course","CourseCode='" & CourseCodes & "'")

Table:
Course
--------
CourseCode, Text, PrimaryKey
Discipline, Text

HtH

Pieter