query across multiple columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to get a count of specific entries across multiple columns? I
have course enrollment data where there are 3 separate fields, each for a
different course. Within each field are entries like "enrolled", "completed",
"cancelled". I want to know how many people have completed all three courses,
just two courses, or just one course. Is there a way i can get this kind of
count across multiple fields?

Thanks
 
Normalize your data with a union query and then run simple totals query to
count the required entries. Each status value should create its own record
rather than have three fields. If you provide some sample data with table
and field names, someone can assist with creating the union query.
 
I'll play around with the Union query as you suggest. But below are some
sample data... the data are all in one table called tblEnrollments (the data
below are comma-delimited). Field names are at the top. If a person hasn't
indicated any interest in a course, the field for that course is blank (not
enrolled, cancelled, or completed).

Thanks again!
rivrras

Student,Course1,Course2,Course3
John C.,Enrolled,Cancelled,
Shannon C.,Completed,Completed,Completed
Sara K.,Enrolled,,Enrolled
Oliver M.,Completed,Cancelled,Cancelled
Anthony N.,Completed,Cancelled,Enrolled
Frank N.,,,Completed
Dorothy D.,,Completed,
Tammy K.,Completed,Completed,Completed
Julie C.,,Enrolled,Completed
Kathleen D.,Completed,Completed,
Harry O.,Completed,Enrolled,Cancelled
Nellie W.,Enrolled,Completed,Completed
Sheila S.,Completed,Enrolled,Enrolled
Kevin L.,,Completed,Cancelled
Nathan B.,Enrolled,,
Kevin B.,Completed,Completed,
Mark M.,,,Enrolled
Karen I.,Completed,Completed,Completed
Elizabeth P.,Completed,Completed,Cancelled
Bart E.,Completed,Completed,Completed
 
I'll play around with the Union query as you suggest. But below are some
sample data... the data are all in one table called tblEnrollments (the data
below are comma-delimited). Field names are at the top. If a person hasn't
indicated any interest in a course, the field for that course is blank (not
enrolled, cancelled, or completed).

Thanks again!
rivrras

Student,Course1,Course2,Course3
John C.,Enrolled,Cancelled,
Shannon C.,Completed,Completed,Completed
Click to expand...

Just jumping in (since Duane implied an invitation with 'someone'):

You can create a Query by going to the SQL window and typing

SELECT [Student], "Course1" AS Course, [Course1] AS Status
FROM yourtable
WHERE [Course1] IS NOT NULL
UNION ALL
SELECT [Student], "Course2", [Course2]
FROM yourtable
WHERE [Course2] IS NOT NULL
UNION ALL
SELECT [Student], "Course3", [Course3]
FROM yourtable
WHERE [Course3] IS NOT NULL;

This will give you
Student, Course, Status
John C., Course1, Enrolled
John C., Course2, Cancelled
Shannon C., Course1, Completed
Shannon C., Course2, Completed
Shannon C., Course3, Completed

If this is authentic data, you may want to seriously look at assigning
a unique StudentID. People's names are not unique; you might have two
students who happen to both be named John Campbell in a class!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks, John. That's helpful. And don't worry, I do have a unique StudentID
in the table - I just didn't include it (or any of the other columns) for
simplicity's sake in my sample. :)

rivrras

John Vinson said:
I'll play around with the Union query as you suggest. But below are some
sample data... the data are all in one table called tblEnrollments (the data
below are comma-delimited). Field names are at the top. If a person hasn't
indicated any interest in a course, the field for that course is blank (not
enrolled, cancelled, or completed).

Thanks again!
rivrras

Student,Course1,Course2,Course3
John C.,Enrolled,Cancelled,
Shannon C.,Completed,Completed,Completed
Click to expand...

Just jumping in (since Duane implied an invitation with 'someone'):

You can create a Query by going to the SQL window and typing

SELECT [Student], "Course1" AS Course, [Course1] AS Status
FROM yourtable
WHERE [Course1] IS NOT NULL
UNION ALL
SELECT [Student], "Course2", [Course2]
FROM yourtable
WHERE [Course2] IS NOT NULL
UNION ALL
SELECT [Student], "Course3", [Course3]
FROM yourtable
WHERE [Course3] IS NOT NULL;

This will give you
Student, Course, Status
John C., Course1, Enrolled
John C., Course2, Cancelled
Shannon C., Course1, Completed
Shannon C., Course2, Completed
Shannon C., Course3, Completed

If this is authentic data, you may want to seriously look at assigning
a unique StudentID. People's names are not unique; you might have two
students who happen to both be named John Campbell in a class!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Thanks for jumping in John.

BTW: I'm working on an excuse to visit a factory nearby so we can "do
lunch". I'll buy as long as we don't discuss normalization and union
queries.

--
Duane Hookom
MS Access MVP


John Vinson said:
I'll play around with the Union query as you suggest. But below are some
sample data... the data are all in one table called tblEnrollments (the data
below are comma-delimited). Field names are at the top. If a person hasn't
indicated any interest in a course, the field for that course is blank (not
enrolled, cancelled, or completed).

Thanks again!
rivrras

Student,Course1,Course2,Course3
John C.,Enrolled,Cancelled,
Shannon C.,Completed,Completed,Completed
Click to expand...

Just jumping in (since Duane implied an invitation with 'someone'):

You can create a Query by going to the SQL window and typing

SELECT [Student], "Course1" AS Course, [Course1] AS Status
FROM yourtable
WHERE [Course1] IS NOT NULL
UNION ALL
SELECT [Student], "Course2", [Course2]
FROM yourtable
WHERE [Course2] IS NOT NULL
UNION ALL
SELECT [Student], "Course3", [Course3]
FROM yourtable
WHERE [Course3] IS NOT NULL;

This will give you
Student, Course, Status
John C., Course1, Enrolled
John C., Course2, Cancelled
Shannon C., Course1, Completed
Shannon C., Course2, Completed
Shannon C., Course3, Completed

If this is authentic data, you may want to seriously look at assigning
a unique StudentID. People's names are not unique; you might have two
students who happen to both be named John Campbell in a class!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top