By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,399 Members | 960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,399 IT Pros & Developers. It's quick & easy.

Report question

P: 12
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 ..
Dec 15 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, Max([vial]) As MaxVial, 
  2. Max([stopper]) As MaxStopper, Max([seals]) As MaxSeals,
  3. Max([codes]) As MaxCodes, Max([other]) As MaxOther
  4. FROM TableName
  5. GROUP BY Name;
  6.  
Mary
Dec 15 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
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.
Expand|Select|Wrap|Line Numbers
  1. 'MaxVal Returns the string associated with the maximum value passed
  2. 'Parameters are passed in pairs,
  3. 'the first being the value and the second the associated string.
  4. Public Function MaxVal(ByVal lngVal As Long, _
  5.                        ByVal strAssoc As String, _
  6.                        ParamArray avarArgs() As Variant) As String
  7.     Dim intIdx As Integer
  8.  
  9.     intIdx = LBound(avarArgs)
  10.     If (UBound(avarArgs) - intIdx) Mod 2 <> 0 Then Stop
  11.     MaxVal = strAssoc
  12.     For intIdx = intIdx To UBound(avarArgs) Step 2
  13.         If avarArgs(intIdx) > lngVal Then
  14.             lngVal = avarArgs(intIdx)
  15.             MaxVal = avarArgs(intIdx + 1)
  16.         End If
  17.     Next intIdx
  18. End Function
The SQL to use it is :
[code]SELECT Name, MaxVal([vial],'Vial',
[stopper],'Stopper',
[seals],'Seals',
Expand|Select|Wrap|Line Numbers
  1. ,'Code',
  2.                     [other],'Other')
  3. FROM TableName
Dec 15 '06 #3

NeoPa
Expert Mod 15k+
P: 31,661
Just noticed it is ALSO grouped by name.
Will post fixed version shortly.
Dec 15 '06 #4

NeoPa
Expert Mod 15k+
P: 31,661
Simply change the SQL to :
[code]SELECT [Name], MaxVal(Max([vial]),'Vial',
Max([stopper]),'Stopper',
Max([seals]),'Seals',
Max(
Expand|Select|Wrap|Line Numbers
  1. ),'Code',
  2.                       Max([other]),'Other')
  3. FROM TableName
  4. 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.
Dec 15 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Nice piece of code Adrian. I think I'll rob it.

Mary
Dec 16 '06 #6

NeoPa
Expert Mod 15k+
P: 31,661
I like my code to be plagiarised (else why would I be here).
Dec 16 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.