ACCESS FEED: Re: [MS_AccessPros] Help with my query

Rabu, 28 Januari 2015

Re: [MS_AccessPros] Help with my query

Sarah-

Looking at your FROM clause, I come up with these table relationships:

Student -> sdetail <- Schedule

+—> MLTC

Why are you doing Outer joins between Student, sdetail, and Schedule? If you have Referential Integrity set up correctly, there should be NO rows in sdetail that don't have matching rows in Student and Schedule. Also, your RIGHT joins are trying to ask for all the rows in sdetail and any matching rows in Student and Schedule, but because you have WHERE criteria on Schedule, that RIGHT JOIN is nullified and will act like an INNER JOIN. I would replace all your RIGHT JOINs with INNER JOIN.

Your new expression makes no sense. If there are no matching rows in MLTC, then all the columns will be Null. I would think you really want something like IIf(IsNull(MLTC.SomeField), < expression using date from from Schedule> , < expression using dates in MLTC >)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 28, 2015, at 3:16 AM, sarahk@schemesoftware.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•