query issues (Access03)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote