here is the SQL for the query in question:
Code:
SELECT qryClassTakenCost.ClassID, qryClassTakenCost.ClassName, qryClassTakenCost.CostPerStudent, qryClassTakenCost.CostPerCourse, qrySeatsUsed.CountOfStudentID, [qryClassTakenCost].[CostPerStudent]*[qrySeatsUsed].[CountOfStudentID]+[qryCourseTotal].[TotalClassCost] AS TotalClassCost, qrySeatsUsed.EmployerContribution
FROM (qryClassTakenCost LEFT JOIN qrySeatsUsed ON qryClassTakenCost.ClassID = qrySeatsUsed.ClassID) LEFT JOIN qryCourseTotal ON qryClassTakenCost.ClassID = qryCourseTotal.ClassID;
The query's last field is a calculated field as follows:
Code:
TotalClassCost: [qryClassTakenCost].[CostPerStudent]*[qrySeatsUsed].[CountOfStudentID]+[qryCourseTotal].[TotalClassCost]
The weird thing that is going on, i noticed first that a specific class nothing was being returned in the TotalClassCost field. I thought it was strange since it had values in both of the required fields in order to produce a result.
To dig further I changed a record to be similar to the one that was having an issue.
* It appears that when the yes/no field (EmployerContribution) is checked, it will not calculate the total.
* Any class that has a value in the two fields required to calculate in the TotalClassCost AND does not have the EmployerContribution field checked, it calculates the total.
Why is it that a yes/no field would cause this issue? And more of a focal point, how do I correct it?