Querying a table with multiple Yes/No fields

[Guest]

Hello,

I have a table that has the students firstname, lastname, and 40 different
Yes/No fields of courses. If a student completes a course, I put a checkmark
(yes) in that course. My question is how do I query the table to pick all the
fields (40) that have yes in them. What is happening right now is once it
filters the first yes/no field,
the remainder (39 fields) don't display the result. Please explain the steps
as if I was a newbie. Thank you.

John

 

[John Spencer]

Step zero - redesign your table structure. You should have three tables.
Table: Students with fields StudentID, FirstName, LastName
Table: Courses with fields CourseID, CourseName
Table: CoursesTaken with fields StudentID and CourseID

If you do redesign your table structure, you will need to design an input
form and subform to handle the data entry. That is a separate question.

Then for each course taken by a student you add a record to CoursesTaken.
Your query becomes really simple. And if someone adds one more course you
don't have to modify anything in your structure to handle this.

I am assuming that you are using the query grid and are not familiar with
the SQL view at all.

If you have to live with your current structure then in the query you need
to put true under each of the fields but on different criteria lines. For
example
Field: Math101
Criteria(1): True
Criteria(2): <<Leave blank>>
Criteria(3): <<Leave blank>>

Field: Math103
Criteria(1): <<Leave blank>>
Criteria(2): True
Criteria(3): <<Leave blank>>

Field: Math103
Criteria(1): <<Leave blank>>
Criteria(2): <<Leave blank>>
Criteria(3): True

By the way, you can add more criteria lines by selecting Insert Rows from
the menu. If you select several criteria rows at one time, you can insert
that many rows at one time.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John,

Thank you for helping me with this. Is there a faster way to put true in the
criteria (grid) for each course? There reason is, having to type true 40
times is very time consuming.

My next question, once I complete the query part, how can I generate a
report for each student to show their completed courses? Thanks.

John

 

[John Spencer]

Nope. There is no easier way than typing True (well you could enter -1
instead of True).

Generating the report is going to be VERY, VERY tedious and difficult due to
the mal-designed table structure.

You would need to build a base query with calculated fields for every one of
the 40 courses.
Field: Math 101: IIF([Math101]),"Math 101",Null)
Field: Math 102: IIF([Math102]), "Math 102", Null)
etc.

Then you need to use logic in the report to show only those values that are
not null. Or you need to write a VBA routine that returns the list of
courses and use that in the query.

Are you sure you don't want to look at redesigning the table structure?

If you current structure is exactly
FirstName, LastName, plus 40 named true false fields then you could perhaps
use something like the following function in a query.

UNTESTED VBA AIR CODE

Public Function fMakeCourseList(sFirstName, sLastName) as String
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim strReturn as String, strSQL as String
Dim LCounter as Long

strSQL = "SELECT * FROM YOURTABLENAME" & _
" WHERE FirstName=""" & sFirstName & _
""" AND LastName=""" & sLastName & """"

Set dbAny = dbEngine(0)(0)
Set rstAny = dbAny.OpenRecordset (strSQL)

If rstAny.RecordCount <> 0 then
For LCounter = 2 to 41
strReturn = strReturn & ", " & rstAny.Fields(LCounter).Name
Next LCounter
End if

StrReturn= Mid(strReturn,3)
FMakeCourseList = strReturn

End Function

Using that your query would look something like

SELECT FirstName, LastName, fMakeCourseList([FirstName],{LastName])
FROM YourTableName

Good Luck
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..