Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    All over, Now in Missouri

    Unanswered: Want VB to get my result

    I have some knowledge on VB, just need help solving a routine, I cannot get the desired results in Access query:

    Scenario: I have two tables in Access that I joined, Left outer join to select all names and then my sub table all of the evaluations (1 per quarter). I need an access rpt to state if individual A has had an evaluation this quarter.

    Query (sample)

    Lastname Evaldate

    Smith 1-Feb-08
    Smith 1-Apr-08
    Doe 8-Apr-08
    Frank 11-Dec-07
    Frank 20-Jan-08

    Here is my output
    - Do they have an eval this quarter, this quarter starts 1-apr-08

    Lastname Evaluation Status

    Smith Completed
    Doe Completed
    Frank Due
    Johnson Due

    Before I get started, I was thinking of doing this with IF, THEN, ELSE

    What do you think

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Have you defined your quarters anywhere in the database?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    lets assume the last evaluation date is actually stored as a date

    you could use an IIF function in the select statement which will return a value on true or false.. you can set that value to be true or false, or somethign else if you so wish

    alternatively you could do it in the presentation layer, and test there if the last evaluation date is in the current or previous quarter.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2008
    All over, Now in Missouri

    Got it in Jet

    This took a few days, but here was the solution

    Thanks for introducing IIF

    SELECT tblPPersonnel.SSN, Sub.lasteval [last eval], IIf(DatePart("q",Date())=DatePart("q",sub.lasteval ),"Completed","Due") AS Status
    FROM tblPPersonnel LEFT JOIN (select tblpevals.ssn, max(tblpevals.evaldate)as lasteval from tblpevals group by tblpevals.ssn) AS sub ON tblPPersonnel.SSN = sub.ssn;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts