Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Unanswered: Should be easy. just not for me (help with query)

    So my synapses aren't firing worth a dern this morning (took vitamins, too). I have a database (of course), with a table for employees (tblEmployees) and a table for Appointments (tblAppointments).

    tblEmployees has fields fldEmpID (PK), fldLName and fldFName
    tblAppointments has fldApptID (PK), fldEID (tblEmployees FK), fldDate and fldAction. fldAction holds a text string of "Attended", "No Show" or "Follow Up" to give a status of their appointment.

    I need to list those employees where their most recent appointment has a fldAction of "Follow Up". I do NOT need to list the most recent appointment with a fldAction of Follow Up, which is all I can seem to get.

    Any ideas? Thanks, as always. Have a great weekend.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First off I'd like to say I loathe your naming convention.

    How do you define their most recent appointment? I assume you mean fldDate has the most recent datetime value? Can two records for a single employee have the same fldDate?

    EDIT: Yarr!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Always nice to begin with some loathing. What's wrong with the naming convention?

    Yeah, most recent is like you described. And no, they won't ever have more than one appointment on the same day. yar...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yarrr - it be as popular as scurvy in these parts, so parrotPolly tells me:
    http://www.dbforums.com/showthread.php?t=1616014
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Arrgh - more from Joe "Blackbeard" Celko - a finer SQL pirate there never was.....is.....no was:
    http://r937.com/nounparis-prepin.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Ah, thanks. I find scurvy to be somewhat enjoyable, personally. That aside, I guess the "tbl"/"fld" stuff is a holdover from variable naming conventions. I just always prefix variables with the datatype. Thanks for pointing it out, though. I'll see if I can let that go on the next db I make...

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by jmahaffie
    Always nice to begin with some loathing.
    I like to bring a little sunshine in to peoples days!

    Select top appointment date per employee
    Code:
    SELECT fldEID
         , Max(fldDate) As [top_date]
    FROM   tblAppointments 
    GROUP
        BY fldEID
    Now, you can use the above select as a derived table in your query (or save it as a separate query in access) and join to it
    Code:
    SELECT appt.*
    FROM   tblAppointments appt
     INNER
      JOIN (
            SELECT fldEID
                 , Max(fldDate) As [top_date]
            FROM   tblAppointments 
            GROUP
                BY fldEID
           ) As [top_appt]
        ON appt.fldEID = top_appt.fldEID
       AND appt.fldDate = top_appt.top_date
    To filter only follow up appts, add a WHERE clause
    Code:
    WHERE  fldAction = "Follow Up"
    If you need to return employee data, add another join to the employee table
    Code:
     INNER
      JOIN tblEmployees emps
        ON appt.fldEID = emps.fldEmpID
    Hope this helps
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Helps? It's perfect, man. Thanks so much. I am forever in your debt. I'll name my next table after you - without the tbl prefix.

Posting Permissions

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