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!
