Why do I get multiple results from a MAX SQL query?-CodePudding

In Access, I have a table named tblBundle and am trying to query the last step that was completed on every bundle in an order.

tblBundle contains: Work Order, Bundle #, BundleLtr, Step, Complete [boolean]

I ultimately want to copy this into vba to create a list of the Bundle #, Ltr, and last step completed for continued data entry on the next step completed.

For some reason I cannot ascertain, the SQL query below returns 2 records for bundle 1 (190, 200) when I believe it should only be returning one record - 200.

SELECT Max(tblBundle.Step) AS intLstep, tblBundle.BundleNbr, tblBundle.BundleLtr, 
tblBundle.Complete
FROM tblBundle
GROUP BY tblBundle.WO, tblBundle.BundleNbr, tblBundle.BundleLtr, tblBundle.Complete
HAVING (((tblBundle.WO)="195687-1-1") AND ((tblBundle.Complete)=True));

Can anyone help me figure out why my query is returning the extra value?

CodePudding user response：

It seems like because your query is breaking down the the result and also returning BundleNbr is causing it to take both Max because its valid.

Also you break this down in your Group By statement and aggregate by that.

Both values of 190 and 200 have a BundleNbr of 1. If you remove that column you'll get the actual max value of 200 (If you want the max of intLStep.

CodePudding user response：

I was able to use this following function to "clean up" my string and remove the additional 'Bundle 1'. This does not solve the original question, but works for my purposes.

'[Removes duplicate values from a string]
Function DeDupeString(ByVal sInput As String, Optional ByVal sDelimiter As 
String = ",") As String

Dim varSection As Variant
Dim sTemp As String

For Each varSection In Split(sInput, sDelimiter)
    If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then
        sTemp = sTemp & sDelimiter & varSection
    End If
Next varSection

DeDupeString = Mid(sTemp, Len(sDelimiter)   1)

End Function

Found From: Removing Duplicate values from a string in VBA