Qry across Fields COUNT/Total

[OnMyOwn_Dan]

MS Access 2003 on Windows XP

Students can request courses each semester. Requests are entered into 5
Fields: Course1....5. Students are allowed to select courses without a forced
choice = Student1 may choose MTH101, BIO112, LIT213, Null, and Null while
Student2 may choose BIO112, LIT213, MTH101, Null, and Null [note the courses
are the same as their program plan indicates but they've chosen in different
order: rationale- it allows them to enter data quicker and easier]. This
represents Student1 and Student2 requests for course registration for Fall
2008.

The struggle = I am now trying to count the number of students who have
requested each course for each term given the random choice-making of above.
I can obtain rudimentary results by filters and manually counting the data
but there has to be a better way.

I've tried parameter queries, reports, WHERE, and have searched posts!
Suggestions?

 

[Bob Barrows [MVP]]

MS Access 2003 on Windows XP

Students can request courses each semester. Requests are entered into
5 Fields: Course1....5. Students are allowed to select courses
without a forced choice = Student1 may choose MTH101, BIO112, LIT213,
Null, and Null while Student2 may choose BIO112, LIT213, MTH101,
Null, and Null [note the courses are the same as their program plan
indicates but they've chosen in different order: rationale- it allows
them to enter data quicker and easier]. This represents Student1 and
Student2 requests for course registration for Fall 2008.

The struggle = I am now trying to count the number of students who
have requested each course for each term given the random
choice-making of above. I can obtain rudimentary results by filters
and manually counting the data but there has to be a better way.

I've tried parameter queries, reports, WHERE, and have searched posts!
Suggestions?

This would have been so much simpler if, when designing your database, you
had thought "narrow and long" rather than "wide and short". A separate table
with 4 fields: StudentID, Semester, ChoiceNumber,CourseID would have made
this child's play:

Select Semester, CourseID, Count(StudentID) As Students
From StudentsCourses
Group By Semester, CourseId

would get you most of the way to what you want.

If you cannot change the design of your database, you will need to get the
data to look like what it would look like in my proposed StudentsCourses
table. To do this, you will need to create a saved query (might as well call
it StudentsCourses) that uses a union query to "fold" your course data. Like
this (you will need to substitute your actual field and table names):

Select StudentID, Semester, 1 As ChoiceNumber,
Course1 As CourseID
From YourCurrentTable
WHERE Course1 is not null
Union All
Select StudentID, Semester, 2,Course2
From YourCurrentTable
WHERE Course2 is not null
Union All
Select StudentID, Semester, 3,Course3
From YourCurrentTable
WHERE Course3 is not null
Union All
Select StudentID, Semester, 4,Course4
From YourCurrentTable
WHERE Course4 is not null
Union All
Select StudentID, Semester, 5,Course5
From YourCurrentTable
WHERE Course5 is not null

Create a query with the above sql, save it as StudentsCourses, and use the
grouping query I suggested above to get your student counts.

 

[OnMyOwn_Dan]

Bob-
TY for the quick reply but it's not working for me. I did the table/fields
substitute and get StudentID is not an aggretable blah blah blah error.
Taking StudentID out of the code yields parameter-like pop ups that I enter
data appropriate responses in, yielding nothing.

So, let's talk DB redesign. I have 668 data instances in the
StudentAdvisements table currently. When StudentID walks in, I need quick
data entry. That's why I built the StudentAdvisements table and made it a
subform of the Student Data Entry_form. That's where I've been selecting
selecting StudentID [main form], Student Registration Advisement [subform
tab], then via drop down selecting the TermID for advisement. There are then
a serious fields for course advisement: CourseTitlesName with all set at Null
x8 fields. Ergo, the previous post reflects this tables data content.

I totally get "narrow and deep". Rather than fields of CourseTitleName1...8,
the table needs to be redesigned with your suggestions as below. Simply, make
a table with StudentID, TermID, CourseID and use this table to replace the
current subform [I left out Choice because they really don't have one]. This
eliminates the cross-field entry of a CourseID. Yield= a student in a term
can be advised for n number of courses making the query much easier.

Any tips on minimizing the number of mouse click or keystrokes on the
subform as I prepare to re-enter 668 data instances?
--
Always thankful for the assistance!

MS Access 2003 on Windows XP

Students can request courses each semester. Requests are entered into
5 Fields: Course1....5. Students are allowed to select courses
without a forced choice = Student1 may choose MTH101, BIO112, LIT213,
Null, and Null while Student2 may choose BIO112, LIT213, MTH101,
Null, and Null [note the courses are the same as their program plan
indicates but they've chosen in different order: rationale- it allows
them to enter data quicker and easier]. This represents Student1 and
Student2 requests for course registration for Fall 2008.

The struggle = I am now trying to count the number of students who
have requested each course for each term given the random
choice-making of above. I can obtain rudimentary results by filters
and manually counting the data but there has to be a better way.

I've tried parameter queries, reports, WHERE, and have searched posts!
Suggestions?

This would have been so much simpler if, when designing your database, you
had thought "narrow and long" rather than "wide and short". A separate table
with 4 fields: StudentID, Semester, ChoiceNumber,CourseID would have made
this child's play:

Select Semester, CourseID, Count(StudentID) As Students
From StudentsCourses
Group By Semester, CourseId

would get you most of the way to what you want.

If you cannot change the design of your database, you will need to get the
data to look like what it would look like in my proposed StudentsCourses
table. To do this, you will need to create a saved query (might as well call
it StudentsCourses) that uses a union query to "fold" your course data. Like
this (you will need to substitute your actual field and table names):

Select StudentID, Semester, 1 As ChoiceNumber,
Course1 As CourseID
From YourCurrentTable
WHERE Course1 is not null
Union All
Select StudentID, Semester, 2,Course2
From YourCurrentTable
WHERE Course2 is not null
Union All
Select StudentID, Semester, 3,Course3
From YourCurrentTable
WHERE Course3 is not null
Union All
Select StudentID, Semester, 4,Course4
From YourCurrentTable
WHERE Course4 is not null
Union All
Select StudentID, Semester, 5,Course5
From YourCurrentTable
WHERE Course5 is not null

Create a query with the above sql, save it as StudentsCourses, and use the
grouping query I suggested above to get your student counts.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

Bob-
TY for the quick reply but it's not working for me. I did the
table/fields substitute and get StudentID is not an aggretable blah
blah blah error.

If you're not going to give me real error messages, I am not going to be
able to help you. Let's see the sql that produced this error.

Taking StudentID out of the code yields
parameter-like pop ups that I enter data appropriate responses in,
yielding nothing.

My crystal ball is not working today ... ;-)

So, let's talk DB redesign. I have 668 data instances in the
StudentAdvisements table currently. When StudentID walks in, I need
quick data entry. That's why I built the StudentAdvisements table and
made it a subform of the Student Data Entry_form. That's where I've
been selecting selecting StudentID [main form], Student Registration
Advisement [subform tab], then via drop down selecting the TermID for
advisement. There are then a serious fields for course advisement:
CourseTitlesName with all set at Null x8 fields. Ergo, the previous
post reflects this tables data content.

I totally get "narrow and deep". Rather than fields of
CourseTitleName1...8, the table needs to be redesigned with your
suggestions as below. Simply, make a table with StudentID, TermID,
CourseID and use this table to replace the current subform [I left
out Choice because they really don't have one]. This eliminates the
cross-field entry of a CourseID. Yield= a student in a term can be
advised for n number of courses making the query much easier.

Ah, so you're taking my advice, are you? How refreshing!
This explains why you did not bother going into detail above. OK

Any tips on minimizing the number of mouse click or keystrokes on the
subform as I prepare to re-enter 668 data instances?

Yes. Use a query (see the union query from my previous post) to populate
your new table. Absolutely no need to retype it.

Insert into newtable (<columns>)
select <columns> from StudentsCourses