Report question

[devagupt]

I have a table with many fields ( 10 fields out of which 6 are for specific product issues). Each of those 6 has a number enter in it via a form.I've created a report and dont need to display these 6 fields. Instead i would like to display the maximum number entered in the fields in a group of records ( as i would sum records based on the persons name and date). I need to display only

eg..

Name vial , stopper , seals , codes , other
Joe 2 5 7 3 5

I would like to display "Seals" in the report as it is the highest number when all of Joe's records are summed.

How would i do that? Please Help ..

[MMcCarthy]

I have a table with many fields ( 10 fields out of which 6 are for specific product issues). Each of those 6 has a number enter in it via a form.I've created a report and dont need to display these 6 fields. Instead i would like to display the maximum number entered in the fields in a group of records ( as i would sum records based on the persons name and date). I need to display only

eg..

Name vial , stopper , seals , codes , other
Joe 2 5 7 3 5

I would like to display "Seals" in the report as it is the highest number when all of Joe's records are summed.

How would i do that? Please Help ..

Try this ...

SELECT Name, Max([vial]) As MaxVial, 
Max([stopper]) As MaxStopper, Max([seals]) As MaxSeals,
Max([codes]) As MaxCodes, Max([other]) As MaxOther
FROM TableName
GROUP BY Name;
 

Mary

[NeoPa]

I would like to display "Seals" in the report as it is the highest number when all of Joe's records are summed.

I tried doing this with multiple IIf() function calls and, though it is possible, it gets very complex very quickly, as more fields are added to the equation.
Instead I wrote a quick function in VBA (needs to be put in a general module or, if only required in the one report, it can be in the code for that report.

'MaxVal Returns the string associated with the maximum value passed
'Parameters are passed in pairs,
'the first being the value and the second the associated string.
Public Function MaxVal(ByVal lngVal As Long, _
                       ByVal strAssoc As String, _
                       ParamArray avarArgs() As Variant) As String
    Dim intIdx As Integer
 
    intIdx = LBound(avarArgs)
    If (UBound(avarArgs) - intIdx) Mod 2 <> 0 Then Stop
    MaxVal = strAssoc
    For intIdx = intIdx To UBound(avarArgs) Step 2
        If avarArgs(intIdx) > lngVal Then
            lngVal = avarArgs(intIdx)
            MaxVal = avarArgs(intIdx + 1)
        End If
    Next intIdx
End Function

The SQL to use it is :
[code]SELECT Name, MaxVal([vial],'Vial',
[stopper],'Stopper',
[seals],'Seals',

,'Code',
                    [other],'Other')
FROM TableName

[NeoPa]

Just noticed it is ALSO grouped by name.
Will post fixed version shortly.

[NeoPa]

Simply change the SQL to :
[code]SELECT [Name], MaxVal(Max([vial]),'Vial',
Max([stopper]),'Stopper',
Max([seals]),'Seals',
Max(

),'Code',
                      Max([other]),'Other')
FROM TableName
GROUP BY [name]

If the word "Vial" is not actually required but the value instead, that is easier but I'm going from your question. If I'm wrong then I can change it quite quickly.

[MMcCarthy]

Nice piece of code Adrian. I think I'll rob it.

Mary

[NeoPa]

I like my code to be plagiarised (else why would I be here).