If/Then expression within a query?

[dan231]

Is there a way to write an IF THEN expression inside a query?

I have an attendance table with AM and PM checks.
I want to have a result of 0, -1 if both the AM and PM checks are -1.

I have this so far, but don't know if it's correct or where to put it so it works, my tests so far have all failed: "If [AM] = -1 and [PM] = -1, then -1,0"

The main end result is to have a report pull only the students who showed up for both the AM and PM sessions. Right now the report shows all students regardless.

Thank you,
Dan

 

[rainman89]

use an

iif statement

http://www.techonthenet.com/access/functions/advanced/iif.php

 

[Alc]

Would this work?

IIf(([AM] = -1 and [PM] = -1), -1, 0)

If not, could you post the SQL version of the query?

 

[dan231]

That works!
Thank you.

My query is used to print completion certificates. I think this stmt will work great for the 1-day classes to weed out the no-shows, but I'm not sure if it'll work on the multiple day classes.

I will definitely implement this tonight and check it out.

Thanks again.

 

[Alc]

Glad to help.

For the multiple day classes, is there an attendance calue stored for each day? If so, you can use nested IIf statements e.g.

IIf(([Monday AM] = -1 and [Monday PM] = -1), ([Tuesday AM] = -1 and [Tuesday PM] = -1), -1, 0)
[

You create the first one for Monday, then replace the -1 part you would expect to get if both conditions were true with another entire case for Tuesday. This can be done for each day of the week and would only return a -1 value if both sessions for all days were true.

 

[dan231]

It's more complicated than that.

The classes are referenced by [ClassID] and the days are set as [DateofClass].

There is another Class Table that lists all the Class ID's and class names.

 

[dan231]

I am on the right track with the IF STMT, but instead of a -1 or 0 I need to only show the -1.

Is it possible to change this to combine [AM] and [PM] in one selection but have the criteria to be -1?

 

[Alc]

What exactly do you mean when you say 'combine' them?
Do you mean if the AM is -1 or the PM is -1?
If so, try

IIf(([AM] = -1 or [PM] = -1), -1, 0)

 

[dan231]

No I need only the records where AM = -1 AND PM = -1

each student will have both fields and it is possible they miss either one. The query needs to pick up only records where the student showed up for both.

 

[Alc]

So does the original statement (below) correctly return -1 where both values are -1 and return 0 where one or both is 0?

IIf(([AM] = -1 and [PM] = -1), -1, 0)

If so, and you don't want the 0 values to be shown at all, put -1 in the criteria for that field, in the query's design mode.

If you're describing something else, I apologize (haven't fully woken up yet, this morning).

 

[dan231]

OMG..that seems so simple [now ]. Thank you. That is precisely what I want. I think I was over thinking this.

 

[Alc]

As my Dad's fond of saying, everything's easy once you know how.
I'll keep my fingers crossed for you.

 

[dan231]

Well it works great for 1-day classes, but not on multi-day classes.

 

[Alc]

How exactly is the data stored, for the multi day classes?

What does the query for the one day classes look like (SQL)?

 

[dan231]

The data is spread out through quite a few tables.

here is the SQL:

SELECT DISTINCT tblBaClassSchedule.ClassDescription, tblBaClassSchedule.ClassScheduleID, [FirstName] & " " & [LastName] AS StudentName, tblBaStudents.LastName, tblBaClassSchedule.StartDate, tblBaClassSchedule.EndDate, DatePart("yyyy",[tblBaClassSchedule.EndDate]) AS ClassYear, forms!frmBaReportsMenu!txtClassMonth AS ClassMonth, forms!frmBaReportsMenu!txtClassDay AS ClassDay, tblBaStudents.StudentID, tblBaStudent_mm_ProfessionalAffiliation.MembershipNumber, tblBaCourses.CourseAIACourseNumber, tblBaCourses.CourseContEdUnits, tblBaCourses.CourseContactHours, forms!frmBaReportsMenu!txtClassDates AS ClassDates, tblBaCourses.CourseLearningUnits, tblBaClassSchedule.ClassDescription AS CourseDescription, tblBaClassSchedule.ClassName, tblBaClassLocation.City & ", " & [StateName] AS Location, forms!frmBaReportsMenu!txtInstructorName AS EmployeeFullName, IIf([FK_ProfessionalAssociationID]=1,"AIA: " & [MembershipNumber],"") AS AIANumber, [CertificateType] & [LicenseCertificateNumber] AS LicenseOrCertification, IIf([FK_StudentCertificateTypeID]=3 Or [FK_StudentCertificateTypeID]=4,"Plumbing License: ","") AS CertificateType, 0 AS EmployeeID, qryBaStudentNameAddress.StudentAddress, qryBaStudentNameAddress.StudentCity, qryBaStudentNameAddress.StudentCity, qryBaStudentNameAddress.StudentState, qryBaStudentNameAddress.StudentState, qryBaStudentNameAddress.StudentZIP, qryBaStudentNameAddress.StudentCityStateZIP, qryBaStudentNameAddress.StudentAddressCityStateZIP, qryBaStudentNameAddress.CompanyName, qryBaStudentNameAddress.StudentlNameWIthMI AS SN, IIf([StateCourseApprovaNumber]>"","State Course Approval Number: " & [StateCourseApprovaNumber],"") AS StateCourseApprovalNumber, tblBaClassSchedule.Status, IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0) AS Expr1, tblBaClassRoster.EnrollmentStatus
FROM tblBaClassAttendance INNER JOIN (((tblBaStudentCertificate RIGHT JOIN (tblStateName RIGHT JOIN (tblEmployee RIGHT JOIN ((((((tblBaClassSchedule INNER JOIN tblBaClassRoster ON tblBaClassSchedule.ClassScheduleID = tblBaClassRoster.FK_ClassScheduleID) INNER JOIN tblBaStudents ON tblBaClassRoster.FK_StudentID = tblBaStudents.StudentID) LEFT JOIN tblBaStudent_mm_ProfessionalAffiliation ON tblBaStudents.StudentID = tblBaStudent_mm_ProfessionalAffiliation.FK_StudentID) INNER JOIN tblBaCourses ON tblBaClassSchedule.FK_CourseID = tblBaCourses.CourseID) LEFT JOIN tblBaClassLocation ON tblBaClassSchedule.FK_LocationID = tblBaClassLocation.LocationID) LEFT JOIN tblBaInstructor_mm_ClassSchedule ON tblBaClassSchedule.ClassScheduleID = tblBaInstructor_mm_ClassSchedule.FK_ClassScheduleID) ON tblEmployee.EmployeeID = tblBaInstructor_mm_ClassSchedule.FK_EmployeeID) ON tblStateName.StateAbbreviation = tblBaClassLocation.State) ON tblBaStudentCertificate.FK_StudentID = tblBaStudents.StudentID) INNER JOIN qryBaStudentNameAddress ON tblBaStudents.StudentID = qryBaStudentNameAddress.StudentID) LEFT JOIN tblBaStateCourseApproval ON tblBaClassSchedule.FK_CourseID = tblBaStateCourseApproval.CourseID_FK) ON tblBaClassAttendance.FK_StudentID = tblBaStudents.StudentID
WHERE (((tblBaClassSchedule.ClassScheduleID)=[forms]![frmBaReportsMenu]![cboGetClasses]) AND ((IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0))=-1) AND ((tblBaClassRoster.EnrollmentStatus)=900))
ORDER BY tblBaStudents.LastName;

This thread led me to add this last part:

AND ((IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0))=-1) AND ((tblBaClassRoster.EnrollmentStatus)=900))

The class dates are in a ClassAttendance tables, which lists the ClassID, StudentID, DateofClass and the AM PM (SignInMorning & SignInAfternoon) fields.

 

[Alc]

You could produce a new query, based on this one. Copy and paste it, then open it and alter the 'Where' clause to show only multi-day classes i.e. where the start date for the class isn't the same as the end date

[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]WHERE (((tblBaClassSchedule.ClassScheduleID)=[forms]![frmBaReportsMenu]![cboGetClasses]) AND ((tblBaClassRoster.EnrollmentStatus)=900)[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]AND [B]([COLOR=black][FONT=Courier New]tblBaClassSchedule.StartDate <> tblBaClassSchedule.EndDate)[/FONT][/COLOR][/B])
[/FONT][/COLOR][COLOR=black][FONT=Courier New]

If you then needed only those courses where the AM attendance and PM attendance were both -1, add both of those to the criteria part of the query.

That should just return those people on multi day courses who attended everything?

 

[dan231]

So I would have 2 queries - 1 for 1-day and 1 for multi-day?
How would I select this option then? The user menu item is just a radio button when selected shows a drop down to select class, then another button runs the query. There is no notification that the class is a multi-day class.

 

[Alc]

Assuming this new query gives you the results you want, you could merge them into one query.

The 'SELECT' part is the same, the 'FROM' part is the same, you'd just be changing the 'WHERE' clause to be either the set of criteria from the first one OR the set from this new one. e.g.

SELECT *****
FROM *****
WHERE (situation 1) or (situation 2);

Situation 1 would be the where clause from the single day query.
Situation 2 would be the new where clause.

 

[dan231]

I think I see where you are going with this. I will test this out and post back.

Thank you for your help so far, it has been very helpful and educational too.

 

[dan231]

ok, so the WHERE should be:

WHERE (((tblBaClassSchedule.ClassScheduleID)=[forms]![frmBaReportsMenu]![cboGetClasses]) AND ((tblBaClassRoster.EnrollmentStatus)=900) OR (((tblBaClassSchedule.ClassScheduleID)=[forms]![frmBaReportsMenu]![cboGetClasses]) AND ((tblBaClassRoster.EnrollmentStatus)=900)
AND (tblBaClassSchedule.StartDate <> tblBaClassSchedule.EndDate))