Need help with code

[DevilDog1978]

SELECT CALSEL_Master_t1.[Item Nbr], max(GREATEST(CV, CV+, LH, LH+))
FROM CALSEL_Master_t1
AS Maximum Required
GROUP BY CALSEL_Master_t1.[Item Nbr]
WITH OWNERACCESS OPTION;

I am trying to get this to work. Basically I want to select the Item Nbr,
CV, CV+, LH and LH+ fields from CALSEL_MASTER_t1. I want this query to return
the max value present in the CV, CV+, LH, LH+ columns as Maximum Required
field. This needs to be grouped by Item Nbr.

Any help would be greatly appreciated.

 

[Douglas J. Steele]

Sorry, but there's no Greatest function in Access, so unless you've written
your own, you're going to have to do a lot more work...

The following (in a stand-along module, not a class module nor a module
associated with a form or report) may work:

Function Greatest(Value1, Value2, Value3, Value4)
Dim Largest

If IsNull(Value1) = False Then
Largest = Value1
End If

If IsNull(Value2) = False Then
If IsNull(Largest) Then
Largest = Value2
Else
If Value2 > Largest Then
Largest = Value2
End If
End If
End If

If IsNull(Value3) = False Then
If IsNull(Largest) Then
Largest = Value3
Else
If Value3 > Largest Then
Largest = Value3
End If
End If
End If

If IsNull(Value4) = False Then
If IsNull(Largest) Then
Largest = Value4
Else
If Value4 > Largest Then
Largest = Value4
End If
End If
End If

Greatest = Largest

End Function

Also, field names shouldn't contain special characters (such as + or even
space). If you cannot rename the fields, you must include them in square
brackets.

SELECT [Item Nbr], Max(Greatest([CV], [CV+], [LH], [LH+])) AS Maximum
Required
FROM CALSEL_Master_t1
GROUP BY [Item Nbr]

 

[Dirk Goldgar]

SELECT CALSEL_Master_t1.[Item Nbr], max(GREATEST(CV, CV+, LH, LH+))
FROM CALSEL_Master_t1
AS Maximum Required
GROUP BY CALSEL_Master_t1.[Item Nbr]
WITH OWNERACCESS OPTION;

I am trying to get this to work. Basically I want to select the Item Nbr,
CV, CV+, LH and LH+ fields from CALSEL_MASTER_t1. I want this query to
return
the max value present in the CV, CV+, LH, LH+ columns as Maximum Required
field. This needs to be grouped by Item Nbr.

Any help would be greatly appreciated.

Note: it's a bad idea to have special characters like "+" or spaces in
field names.

You might approach this by creating a VBA function like this and storing it
in a standard module:

'------ start of code ------
Function MaxInList(ParamArray ListValue() As Variant) As Variant

Dim I As Long
Dim MaxVal As Variant
Dim ListVal As Variant

MaxVal = Null

For I = LBound(ListValue) To UBound(ListValue)

ListVal = ListValue(I)
If Not IsNull(ListVal) Then
If IsNull(MaxVal) Then
MaxVal = ListVal
Else
If ListVal > MaxVal Then
MaxVal = ListVal
End If
End If
End If

Next I

MaxInList = MaxVal

End Function
'------ end of code ------

Then write your query like this:

SELECT
[Item Nbr],
Max(MaxInList(CV, [CV+], [LH], [LH+])) AS Maximum Required
FROM CALSEL_Master_t1
GROUP BY [Item Nbr]
WITH OWNERACCESS OPTION;

I haven't done more than the most cursory test of that, but something like
it ought to work. Be aware that calling a VBA function like this is going
to make your query run slower than it otherwise might. You'll have to
decide if it runs fast enough for your purposes.

 

[DevilDog1978]

Thanks for your help. I am still new to this, what portion of the VBA do I
need to change to reflect my information?

SELECT CALSEL_Master_t1.[Item Nbr], max(GREATEST(CV, CV+, LH, LH+))
FROM CALSEL_Master_t1
AS Maximum Required
GROUP BY CALSEL_Master_t1.[Item Nbr]
WITH OWNERACCESS OPTION;

I am trying to get this to work. Basically I want to select the Item Nbr,
CV, CV+, LH and LH+ fields from CALSEL_MASTER_t1. I want this query to
return
the max value present in the CV, CV+, LH, LH+ columns as Maximum Required
field. This needs to be grouped by Item Nbr.

Any help would be greatly appreciated.

Note: it's a bad idea to have special characters like "+" or spaces in
field names.

You might approach this by creating a VBA function like this and storing it
in a standard module:

'------ start of code ------
Function MaxInList(ParamArray ListValue() As Variant) As Variant

Dim I As Long
Dim MaxVal As Variant
Dim ListVal As Variant

MaxVal = Null

For I = LBound(ListValue) To UBound(ListValue)

ListVal = ListValue(I)
If Not IsNull(ListVal) Then
If IsNull(MaxVal) Then
MaxVal = ListVal
Else
If ListVal > MaxVal Then
MaxVal = ListVal
End If
End If
End If

Next I

MaxInList = MaxVal

End Function
'------ end of code ------

Then write your query like this:

SELECT
[Item Nbr],
Max(MaxInList(CV, [CV+], [LH], [LH+])) AS Maximum Required
FROM CALSEL_Master_t1
GROUP BY [Item Nbr]
WITH OWNERACCESS OPTION;

I haven't done more than the most cursory test of that, but something like
it ought to work. Be aware that calling a VBA function like this is going
to make your query run slower than it otherwise might. You'll have to
decide if it runs fast enough for your purposes.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for your help. I am still new to this, what portion of the VBA do I
need to change to reflect my information?

You wouldn't change the VBA at all, just copy and paste the function into a
standard module. If you are creating a new module for this purpose, do not
save the module with the same name as the function.

The only thing you might change is the SQL of the query, and I think I got
it right based on the table and field names you originally posted. Try it
out and see if it works. Please post back and let us know how it goes.

 

[James A. Fortune]

SELECT CALSEL_Master_t1.[Item Nbr], max(GREATEST(CV, CV+, LH, LH+))
FROM CALSEL_Master_t1
AS Maximum Required
GROUP BY CALSEL_Master_t1.[Item Nbr]
WITH OWNERACCESS OPTION;

I am trying to get this to work. Basically I want to select the Item Nbr,
CV, CV+, LH and LH+ fields from CALSEL_MASTER_t1. I want this query to
return
the max value present in the CV, CV+, LH, LH+ columns as Maximum Required
field. This needs to be grouped by Item Nbr.

Any help would be greatly appreciated.

Note: it's a bad idea to have special characters like "+" or spaces in
field names.

You might approach this by creating a VBA function like this and storing
it in a standard module:

'------ start of code ------
Function MaxInList(ParamArray ListValue() As Variant) As Variant

Dim I As Long
Dim MaxVal As Variant
Dim ListVal As Variant

MaxVal = Null

For I = LBound(ListValue) To UBound(ListValue)

ListVal = ListValue(I)
If Not IsNull(ListVal) Then
If IsNull(MaxVal) Then
MaxVal = ListVal
Else
If ListVal > MaxVal Then
MaxVal = ListVal
End If
End If
End If

Next I

MaxInList = MaxVal

End Function
'------ end of code ------

Then write your query like this:

SELECT
[Item Nbr],
Max(MaxInList(CV, [CV+], [LH], [LH+])) AS Maximum Required
FROM CALSEL_Master_t1
GROUP BY [Item Nbr]
WITH OWNERACCESS OPTION;

I haven't done more than the most cursory test of that, but something
like it ought to work. Be aware that calling a VBA function like this
is going to make your query run slower than it otherwise might. You'll
have to decide if it runs fast enough for your purposes.

For a maximum of maximums it doesn't matter whether you maximize the
rows first or the columns first. So I'd guess that a single User
Defined Function (UDF) evaluation, using the same function you posted,
but using max()'s for arguments would be arguably supreme for most cases.

James A. Fortune
(e-mail address removed)

 

[Dirk Goldgar]

For a maximum of maximums it doesn't matter whether you maximize the rows
first or the columns first. So I'd guess that a single User Defined
Function (UDF) evaluation, using the same function you posted, but using
max()'s for arguments would be arguably supreme for most cases.

I agree. That occurred to me as I posted, but thought the logic of the
example would be easier to follow, so I left it as is.

 

[James A. Fortune]

I agree. That occurred to me as I posted, but thought the logic of the
example would be easier to follow, so I left it as is.

Yes, that makes sense. I definitely agree with your choice given the
OP. I hesitated to mention it also.

James A. Fortune
(e-mail address removed)