MSAccess - Query Help: Finding Max Revision for each Record

I need help figuring out why my Access queries don’t work properly. I hope I can explain the problem well enough; sorry for the length!

My database contains data about documents, such as DocumentNumber (NoDoc), TypeDoc, Revision, CertificateDate, etc which relate to our Widget products. One document might be relevant to more than one Widget, so the tblDocumentInformation has an autonumber (CertificateIndex) counter which relates in a one-to-many to another table (tblWidgetCertification), which has fields for WidgetID to match with CertificateIndex. Each document comes from one specific country, as well (tblCountry, with Country and CountryID). There are 5 document types, called TypeDoc (Certificate, DataSheet, Letter, Correspondence, Other).

What I’m trying to do is find the most recent document per Widget, per Country. I want the search to come back with the most recent result of each document type, so the greatest revision of Certificate, DataSheet, Letter, etc.

I found a couple of websites that describe how to do this, in theory, but I can’t seem to get it to work in practise! Here is one of the sites: Microsoft Support

I have created two queries; one to find the MaxRevision (qryMaxDocument), and the other to collect all the other information for each entry so that they can be displayed on a report (qryRecentDocument).

As far as I can tell, qryMaxDocument works. The results look to be correct: e.g. for DocumentNumber B123 I get something like B123 Certificate Rev 3, the B123 Letter Rev 1 and the B123 DataSheet Rev 2, all of the last revision in the list (I checked in manually).

Here is my SQL for qryMaxDocument:



SELECT 
Max((IsAlphaRev([Revision]))) AS Rev, 
StripToAlphaNums([NoDoc]) AS DocNo, 
tblDocumentInformation.TypeDoc

FROM tblDocumentInformation

GROUP BY StripToAlphaNums([NoDoc]), tblDocumentInformation.TypeDoc;


The two functions, IsAlphaRev() and StripToAlphaNums() are used to deal with the various ways people have historically entered data into the database. IsAlphaRev is essentially the same as Val(); it treats alpha revision entries (such as “N/A”) as 0, and otherwise treats everything else as the number that it actually is, but it keeps the field as a text string instead of converting to number field (I think…!) so that I can match it up to the original tblDocumentInformation in my next query. StripToAlphaNums removes all non-alphanumeric characters from the NoDoc, so that B123, B-123 and B 123 are all considered to be the same entry.

As I said, qryMaxDocument works, giving me 270 distinct results.

Now, following the instruction in the link above, I made a second query, qryRecentDocument, with the table for qryMaxDocument linked to the tblDocumentInformation. In the grid, I put the three fields from qryMaxDocument (DocNo, Rev and TypeDoc).

Right away, there’s a problem… where before I was getting 270 results (what I estimated I’d get), I now only get 38. This new query doesn’t DO anything new. Even if I add other fields, such as CountryName, WidgetName, I don’t get all of the records, only part of it! I have added Country, WidgetName, certificationIndex, and CertificateDate, and the best I can come up with is 176 results, but just looking at them I know I’m missing a document from Austria for SuperWidget405, for example. I don’t know why I’m not getting the same number of results as qryMaxDocument, and I have no idea where to look to fix it!

Here is my code for qryRecentDocument:



SELECT DISTINCT 
qryMAXDocument.Rev, 
qryMAXDocument.DocNo, 
qryMAXDocument.TypeDoc, 
tblDocumentInformation.CertificationIndex, 
tblWidgetProducts.WidgetName, 
tblDocumentInformation.CertificateDate, 
tblDocumentInformation.CountryID

FROM 
(qryMAXDocument INNER JOIN (tblCountry INNER JOIN tblDocumentInformation ON tblCountry.CountryID = tblDocumentInformation.CountryID) ON (qryMAXDocument.Rev = tblDocumentInformation.Revision) AND (qryMAXDocument.DocNo = tblDocumentInformation.NoDoc)) INNER JOIN (tblWidgetProducts INNER JOIN tblWidgetCertification ON tblWidgetProducts.WidgetID = tblWidgetCertification.WidgetID) ON tblDocumentInformation.CertificationIndex = tblWidgetCertification.CertificationIndex

GROUP BY 
qryMAXDocument.Rev, 
qryMAXDocument.DocNo, 
qryMAXDocument.TypeDoc, 
tblDocumentInformation.CertificationIndex, 
tblWidgetProducts.WidgetName, 
tblDocumentInformation.CertificateDate, 
tblDocumentInformation.CountryID;



I just can’t figure out what I’ve done wrong, especially since qryMaxDocument gives me the correct results! Am I entirely on the wrong track? How can I get these queries to return the most recent revision for each document type for each document number, based on user-selected WidgetName and Country (I’ll be adding that later… I know how to do that!)? Could it be a problem with my two functions?

I suppose it doesn’t hurt to post the functions code here, too:



Public Function StripToAlphaNums(ByVal StringIn As String) As String
  Dim intX As Integer
  Dim intLen As Integer
  Dim strTemp As String
  Dim strSng As String * 1
  
  intLen = Len(StringIn)
  For intX = 1 To intLen
    strSng = Mid(StringIn, intX, 1)
    Select Case Asc(strSng)
      Case 48 To 57, 65 To 90, 97 To 122
        strTemp = strTemp & strSng
    End Select
  Next
  StripToAlphaNums = strTemp
End Function


Public Function IsAlphaRev(ByVal StringIn As String) As String
Dim sRevision As Variant

If Not IsNumeric([StringIn]) Then sRevision = 0 Else sRevision = StringIn

IsAlphaRev = sRevision
End Function


I would be very, very grateful for your help… this is the last thing I need to work out before I can make this database go “live”! Thank you so much!

I have a hunch - please don’t hold me responsible if I’m wrong - but I think the problem is with qryRecentDocument, where you are using the “DISTINCT” keyword. Possibly complicating the issue is the Country table, which you don’t really need at this stage.

I would try removing that table for the time being, or drawing CountryID from tblCountry, and possibly removing the “DISTINCT” keyword (toggle “Unique Values” on the query’s property sheet).

I’m going to have to do something similar shortly, please keep us posted on how you sort it out!

Is DocNo unique to each document, or each revision? one difference I see is that the first query is doing just a SELECT, while the second is doing a SELECT DISTINCT, and then grouping by DocNo - if all revisions of the document share the same DocNo, they will just collapse into one in the second query. Without looking at the outputs, it’s hard to see where the problem lies, but I suspect it might also be in the GROUP BY clause in the second query.

I think I have tried removing DISTINCT from the second query, but I’ll certainly give it a shot again tomorrow to be sure. I have tried so many permutations, I just had to give up today, because I couldn’t stand looking at it any more! I admit that I don’t really understand what the GROUP BY clause has to do with anything, but it certainly causes enough errors if it isn’t there!

The problem is that this database has weird data in it, but I can’t go back and change any of it, so I have to work with it. The DocNo is not unique to a revision; we have 33 revisions of B-123, for example (though sometimes it’s B123, sometimes B 123 etc). Also, just to complicate things, we might have B123 Certificate, and then a DataSheet for B123 that has a different revision number. And to make things even MORE complicated, some countries just accept the DataSheet and/or the Certificate from another country, so we might have C345 from Canada and C345 from the USA. We just have to go with whatever information is actually available on the document when the country sends it to us, so sometimes it’s a wild guess. We even have some that are DocNo N/A Revision N/A. Very frustrating.

Basically, if I just take the DocumentInformation table and only doMAX(IsAlphaRev(Revision)) and StripToAlphaNums(NoDoc), I get 313 results (I messed up when I said 270). So theoretically, that’s result for every document… but as I said before, sometimes the NoDoc is the same for two different countries, so those have to come back as being two different things. So I should have more than 313 results… maybe 320 or 330. So my initial search has to include the Country. And then later, to get the Widget, I need the CertificationIndex, because that’s how the relationships are built in the database. If I add any other columns to either query, I start getting multiple results, or all records, or some but not all… I just don’t understand. Perhaps there’s a better way to organize this search? Sometimes I think the Max() function doesn’t work as I think it should :frowning:

And I haven’t even gotten to the part where I need to do the search based on the IsAlphaRev and StripToAlphaNums results, but I need to use the original, unaltered entries to generate at filename on the report! I’m thinking chaining yet another query to this mess and using DLookup, but I don’t know. Unless I can get the CertificationIndex out of the initial search, without it giving me 5000+results as it seems to want to do, I can’t see how to do that, either.

Really, you’d think this could be a lot easier! What’s so hard about finding the record with the max number in one column, sorted based on the entries in two others (DocNo and Country)? We can put a man on the moon, but this, this is hard!