Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query issues (Access03)

    I have a list of certifications that include several incidents of two different versions of the same thing, for example
    BLS for Healthcare Providers and
    BLS for Healthcare Provider Renewal.

    My problem is that I want the last item of those two displayed. I have gotten the process set up to give me just the last of each of the items, but don't know how to limit just the one of those two. For example I get the BLS for Healthcare Provider 2003 date and the BLS for Healthcare Provider Renewal 2005 date. Here are the steps and SQL from the query grids.

    qryWhoCert1
    SELECT tblCombinedCertsDidDidnt.LearnerID, tblCombinedCertsDidDidnt.ClassName, tblCombinedCertsDidDidnt.ClassNumber, tblCombinedCertsDidDidnt.ClassID, tblCombinedCertsDidDidnt.DateOfClassStart, tblCombinedCertsDidDidnt.Due
    FROM tblCombinedCertsDidDidnt;

    qryWhoCert2
    SELECT qryWhoCert1.LearnerID, Max(qryWhoCert1.DateOfClassStart) AS MaxOfDateOfClassStart, qryWhoCert1.ClassName
    FROM qryWhoCert1
    GROUP BY qryWhoCert1.LearnerID, qryWhoCert1.ClassName;

    qryWhoCert3
    SELECT qryWhoCert1.LearnerID, qryWhoCert1.ClassName, qryWhoCert1.ClassNumber, qryWhoCert1.ClassID, qryWhoCert2.MaxOfDateOfClassStart, qryWhoCert1.DateOfClassStart, qryWhoCert1.Due
    FROM qryWhoCert2 INNER JOIN qryWhoCert1 ON (qryWhoCert2.LearnerID = qryWhoCert1.LearnerID) AND (qryWhoCert2.ClassName = qryWhoCert1.ClassName);

    Thank you.

    Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query issues (Access03)

    How can we know that "BLS for Healthcare Providers" and "BLS for Healthcare Provider Renewal" are different versions of the same thing? This is not a facetious question - it may be obvious to you, but it isn't to me, and certainly not to a computer.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query issues (Access03)

    Good question "BLS for Healthcare Providers" is the full version and "BLS for Healthcare Provider Renewal" is the version that a person takes if they have already had the other version previous and they haven't gone pass the expiration date.

    The only difference the computer knows is the two different names. That is why I can't figure a way to only get one, the latest, to report.

    I am not sure I answered your question?

    Fay

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query issues (Access03)

    Are there other such pairs (or even triples, etc.)?

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query issues (Access03)

    Yes there are several. I think they are all pairs.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query issues (Access03)

    You'll have to add an extra field to the table and use this to specify which items belong together. I'd use a number field. Assign different values to "different" records and the same value to related records. For example, 'First Aid with CPR' would be assigned 20 and "BLS for Healthcare Providers" and "BLS for Healthcare Provider Renewal" would both be assigned 37. The actual numbers are arbitrary, just start at 1.
    You can then use the new field in your queries.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query issues (Access03)

    Thanks Hans. That sounds like the hammer-handed way I was thinking about. I just expected there was a more elegant Access way of doing it that I wasn't smart enough to know about. I will get on it. Fay

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query issues (Access03)

    You have to tell Access which items belong together. It is not intelligent enough to guess that. You could create complicated nested IIf expressions, but using an extra field is a 'clean' way of doing it.

Posting Permissions

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