David Trickett
07-29-2003, 01:32 AM
The table (“Main”) contains, among others, fields for case reference, starting date, date 12 month report done, date 12 month report approved, date 24 month report done, date 24 month report approved… and so on up to 60 months. There is also a table “Caseworkers”.
The procedure is that the worker enters the report date, and the manager then enters the approval date.
I need to present the managers with a list of reports to approve as at the current date. Because of delays & general laziness on the part of the staff it is possible for more than one report to require approval.
I have succeeded in listing the cases where there is at least one report to approve. The problem is that if any report is due for approval it lists all reports done, even if they have been approved. So what I need is a query that will return the report sequence (12 month etc) and date done only where it has not been approved.
The existing SQL (slightly abbreviated) is
SELECT Main.Reference, Caseworkers.CWname, Main.Datereviewdone12, Main.Datereviewdone24, Main.Datereviewdone36, Main.Datereviewdone48, Main.Datereviewdone60
FROM Main INNER JOIN Caseworkers ON Main.CWinitials = Caseworkers.CWinitials
WHERE (((Main.Datereviewdone12) Is Not Null) AND (([Main]![Mandate12]) Is Null) AND ((Caseworkers.Manname)=getmanager())) OR (((Main.Datereviewdone24) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate24]) Is Null)) OR (((Main.Datereviewdone36) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate36]) Is Null)) OR (((Main.Datereviewdone48) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate48]) Is Null)) OR (((Main.Datereviewdone60) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate60]) Is Null))
ORDER BY Caseworkers.CWname;
The term “((Caseworkers.Manname)=getmanager())” selects all members of a given manager’s team, “datereviewdone12” etc are the dates the reports were done, “mandate12” etc the dates approved.
I am exploring a few workrounds, but it would be nice to do the job properly – apart from anything else the query would be useful in other contexts.
I know this is complex, and may not even be possible, which is probably why my very little brain can’t get round it. Any help would be much appreciated.
Thanks
David Trickett
The procedure is that the worker enters the report date, and the manager then enters the approval date.
I need to present the managers with a list of reports to approve as at the current date. Because of delays & general laziness on the part of the staff it is possible for more than one report to require approval.
I have succeeded in listing the cases where there is at least one report to approve. The problem is that if any report is due for approval it lists all reports done, even if they have been approved. So what I need is a query that will return the report sequence (12 month etc) and date done only where it has not been approved.
The existing SQL (slightly abbreviated) is
SELECT Main.Reference, Caseworkers.CWname, Main.Datereviewdone12, Main.Datereviewdone24, Main.Datereviewdone36, Main.Datereviewdone48, Main.Datereviewdone60
FROM Main INNER JOIN Caseworkers ON Main.CWinitials = Caseworkers.CWinitials
WHERE (((Main.Datereviewdone12) Is Not Null) AND (([Main]![Mandate12]) Is Null) AND ((Caseworkers.Manname)=getmanager())) OR (((Main.Datereviewdone24) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate24]) Is Null)) OR (((Main.Datereviewdone36) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate36]) Is Null)) OR (((Main.Datereviewdone48) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate48]) Is Null)) OR (((Main.Datereviewdone60) Is Not Null) AND ((Caseworkers.Manname)=getmanager()) AND (([Main]![Mandate60]) Is Null))
ORDER BY Caseworkers.CWname;
The term “((Caseworkers.Manname)=getmanager())” selects all members of a given manager’s team, “datereviewdone12” etc are the dates the reports were done, “mandate12” etc the dates approved.
I am exploring a few workrounds, but it would be nice to do the job properly – apart from anything else the query would be useful in other contexts.
I know this is complex, and may not even be possible, which is probably why my very little brain can’t get round it. Any help would be much appreciated.
Thanks
David Trickett