Re: Access 2000 to Access 2003 conversion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The application flow is:
1. switchboard menu - user selects a form to show teachers who are
absent awaiting a substitute assignment
2. in open absence review screen, the user can double click a row to
launch the assignment fulfillment screen
3. The assignment fulfillment screen shows the absence at the top, and
then available substitutes in a grid in the center. This subform has
an additional subform, and both of these are in "datasheet" mode. The
query that displays records for this form is a multi table join. The
options from the form form are [assign] , [log a call], [close], and
[switch view].

The most "peculiar" feature coded in the form is a "flag column". Not
really knowing access at the time, I wrote dlookup macro for the flag
to return whether or not the "available sub" had been called (the call
being in a call log table for the absence record). IF this "flag" is
set, then the record is to be highlighted so the operator does not call
the same substitute twice.

The expression for that "flag" is:
=Nz(DLookUp("[AbsenceId]","Absence_Call_Log","[AbsenceId]=" &
[Forms]![Fill Absences]!AbsenceID & " AND [SubstituteID]=" &
[Forms]![Fill Absences]!Available_Substitutes.Form!SubstituteID),0)

I was thinking I would remove this dlookup and add a subquery as a
SELECT clause column and see if that helps. I know in Oracle you would
not be able to use two tables to outer join to a single table
(assignments and subs to the call log table). However, I could
probably create intermediate queries and handle this problem.

The query gets kind of complex because it has to:
show subs who
1. are willing to work at this school
2. are certified in the subject, or willing to work in the subject, or
simply a sub
3. will work a full day if it's a full day absence and will work half
days if it is an AM or PM absence
4. Is not already assigned for this day for any non-canceled assignment
5. IS willing to work for the period of all days the teacher absence
entails, factoring out weekends and any holiday dates
6. prioritizing the order of selection by:
a) committed subs for this specific school
b) subs in the "committed" program for the district
c) substitute type (professional, guest, retired)
d) certified/willing status
e) last, first

oops, who has not declined an assignment for this specific day (only
applying to single day absences)

Anyway, I do appreciate all your advice.

Oh, do you recommend any good books on Access 2003? Right now I have
three MS access 2000 books, 2 of which I've never cracked but wanted to
- you know, deadlines, anyway:
Beginning Access 2000 VBA - Wrox
Sybex Access 2000 developer handbook: volume 1
Sybex Access 2000 vba

.

Relevant Pages

  • Re: Access 2000 to Access 2003 conversion
    ... trace the source of the crash. ... in open absence review screen, the user can double click a row to ... > launch the assignment fulfillment screen ... > (assignments and subs to the call log table). ...
    (microsoft.public.access.conversion)
  • Re: AVG7.5 update 09/08/2007 (269.11.11/944)
    ... *depute* is verb not a noun meaning to Transfer power to ... someone, appoint as a substitute, give an assignment to to a ...
    (uk.people.silversurfers)
  • Re: Wheres Joy Behar in the View
    ... Ususally they mention when one of the cast is either sick or on assignment and don't recall anything was mentioned about her absence. ...
    (rec.arts.tv)
  • Re: Why We Dont Want "Free" Medical Care
    ... diplomacy is a substitute in the absence of overwhelming force ... Remove clothing to email ...
    (rec.travel.europe)
  • Re: Derived TYPEs and defined assignments
    ... Richard E Maine schreef: ... But substitute some derived type instead of complex. ... I wanted to prevent any assignment of the derived type in question ...
    (comp.lang.fortran)