Dear members, I need help with revising my query.
this is my old query:
INSERT INTO sdtemp ( [schedule id], [shift date], [student id], [sdetail id], arrival, departure, private, subsidy )
SELECT sdetail.[schedule id], Schedule.[shift date], sdetail.[student id], sdetail.[sdetail id], sdetail.arrival, sdetail.departure, sdetail.private, student.subsidy
FROM student RIGHT JOIN (Schedule RIGHT JOIN sdetail ON Schedule.[schedule id] = sdetail.[schedule id]) ON student.[student id] = sdetail.[student id]
WHERE (((Schedule.[shift date]) Between [Forms]![Billing]![bill from] And [Forms]![Billing]![bill to]) AND ((sdetail.private)=True) AND ((Schedule.generated)=True) AND ((student.[hold billing])="N") AND ((sdetail.billed)=False) AND ((sdetail.attended)=True) AND ((sdetail.hold)=False)) AND studentDaySched(Schedule.[shift date], [student].[Sat],[student].[Sun],[student].[mon],[student].[tue],[student].[wed],[student].[thu],[student].[fri],[student].[subsidy]) = True
ORDER BY Schedule.[shift date], sdetail.[student id];
The old query works fine, it selects the student records for the dates I request and inserts into the sdtemp table.
I need to modify this query, The student.subsidy can be in another table called 'MLTC' and can vary by enrollment date. However the 'MLTC' table is optional and might not exist for some students.
This is how I modified my query:
INSERT INTO sdtemp ( [schedule id], [shift date], [student id], [sdetail id], arrival, departure, private, subsidy )
SELECT sdetail.[schedule id], Schedule.[shift date], sdetail.[student id], sdetail.[sdetail id], sdetail.arrival, sdetail.departure, sdetail.private, IIf((nz([mltc].[disenrollmentdate],"")>=Schedule.[shift date] And MLTC.enrollmentdate<=Schedule.[shift date]) Or (MLTC.enrollmentdate<=Schedule.[shift date] And MLTC.disenrollmentdate Is Null),mltc.subsidy,student.subsidy)
FROM (student RIGHT JOIN (Schedule RIGHT JOIN sdetail ON Schedule.[schedule id] = sdetail.[schedule id]) ON student.[student id] = sdetail.[student id]) LEFT JOIN MLTC ON sdetail.[student id] = MLTC.studentid
WHERE (((Schedule.[shift date]) Between [Forms]![Billing]![bill from] And [Forms]![Billing]![bill to]) AND ((sdetail.private)=True) AND ((Schedule.generated)=True) AND ((student.[hold billing])="N") AND ((sdetail.billed)=False) AND ((sdetail.attended)=True) AND ((sdetail.hold)=False) AND ((studentDaySched([Schedule].[shift date],[student].[Sat],[student].[Sun],[student].[mon],[student].[tue],[student].[wed],[student].[thu],[student].[fri],[student].[subsidy]))=True))
ORDER BY Schedule.[shift date], sdetail.[student id];
I added:
IIf((nz([mltc].[disenrollmentdate],"")>=Schedule.[shift date] And MLTC.enrollmentdate<=Schedule.[shift date]) Or (MLTC.enrollmentdate<=Schedule.[shift date] And MLTC.disenrollmentdate Is Null),mltc.subsidy,student.subsidy)
so that if there is an entry in the MLTC table it would select the code from that table , otherwise from the STUDENT table. But this does not work, if the student has no entry in the MLTC table it does not select the student,subsidy field, it results in #error.
What am I doing wrong?. I am using Access2003 on Win 8.1.
As always all help is greatly appreciated.
Thanks
Sarah
Posted by: sarahk@schemesoftware.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar