Results 1 to 3 of 3
  1. #1
    kylem4711 is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    1

    Help With an expression

    Hello All, Thanks for the help

    Basically I have 4 different classes that a Manager can take and to be cosidered "Complete" they have to complete 2 out of the 4. Any combination at any time. I know I would be using an If Statement, but i am not sure what direction to go in. any suggestions?

    here is what I am thinking.

    IIf([Job Category]='Manager' And [Course] In ('3L00643','L00640','L00641','L00642') Here is where i get stuck. i want to say something like And If the person has completed two or more out of the four classes, show the max date of the classes they have completed.



    any suggestions? anything i can look up if this has been covered before?

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Use divide and conquer strategy:-

    Make a query first to get the statistics of required classes completed.
    If it is 2 or more, then you can then extract its date of completion.

  3. #3
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    You might look into using the DCount/DMax functions in combination with IIF.

    Something like:

    IIF([Job Category]='Manager',IIf(DCount("*","[tblCoursesAttended]","[EmployeeID]=" & [EmployeeID] & " AND [Course] In ('3L00643','L00640','L00641','L00642'))>=2,Dmax("[CourseDate]","tblCoursesAttended","[EmployeeID]=" & [EmployeeID]),"Has not completed Manager Requirements"),"Non Manager Response Here")

    However, if you have thousands of records this is likely to run pretty slowly. A sql solution would run faster but right now I don't have the time to spend on deriving it for you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Expression Builder
    By mistaken_myst in forum Access
    Replies: 2
    Last Post: 05-07-2008, 01:30 PM
  2. Replies: 0
    Last Post: 11-04-2006, 02:31 AM
  3. Query expression for form
    By pjacob in forum Forms
    Replies: 1
    Last Post: 10-10-2006, 09:12 PM
  4. Field gets data from Expression?
    By Willot in forum Access
    Replies: 3
    Last Post: 12-10-2005, 06:28 PM
  5. Replies: 0
    Last Post: 12-05-2005, 04:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums