Finding maxes including a text field 1

[efinnen]

I appreciate any help that could be given with this problem. I really feel I should know but my brain isn’t working.. So I’m throwing myself on the mercy of Tek-Tips and hope some body can help. Any help/guidance would be greatly appreciated.

I have a three field table which corresponds to COMBO, QUALIFIER and VALUE.

The COMBO field is effectively a concatenation of each Location and the test that is run at that location.

Now comes the part that I feel stupid about.

I need to generate the overall max for each COMBO. However if the max has a J in the qualifier I need to concatenate that at the end. Such that in this case I would want an end result similar to the table below. I realize this will probably have to be some VB code or several queries which I’d be fine with.

Cut from much larger table 3 fields
tblEstimatedMaxes
Value Qualifier combo
22.7 J DP1007_PBZN
11800 DP1007_TCE
3.46 J DP1007_TMB124
3.4 DP1007_TMB124
7.35 DP1007_TMB135
0.58 J DP1007_TMB135
407 DP1007_VC


DESIRED OUTPUT 2 fields
COMBO MAX
DP1007_PBZN 22.7 J
DP1007_TCE 11800
DP1007_TMB124 3.46 J
DP1007_TMB135 7.35
DP1007_VC 407


Thanks again
-eric
efinnen@hotmail.com

ps : sorry my tables didn't come out prettier

 

[PHV]

A starting point:
SELECT COMBO, Max([Value] & IIf([Qualifier]='J',' J','')) AS [MAX]
FROM yourTable
GROUP BY COMBO;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[efinnen]

Thanks for the starting point. I actually had something like that designed albeit not as compact as yours.

Unfortunately it still presents the same problem with finding the max as a string (i.e. if two similar combo values has a value of 2300 (with a J or not) and the next one was 92 (with or without a J) It would classify the max as 92.

Also they aren't always all J's sometimes they are other letters.

-eric

 

[PHV]

What is the data type of Value ?
If not numeric but populating with only numbers:
SELECT COMBO, Max(Val([Value]) & [Qualifier]) AS [MAX]
FROM yourTable
GROUP BY COMBO;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[efinnen]

The Value field is defined as a number. I double checked!

An example of the dilemma I'm having is below.

A cut from bigger table
Qualifier Value COMBO
57.1 DP1007_EBZ
J 54.3 DP1007_EBZ
208 DP1007_IPBZ
22.7 DP1007_PBZN
J 22.6 DP1007_PBZN
11800 DP1007_TCE
J 2000 DP1007_TMB124
92 DP1007_TMB124
J 8.58 DP1007_TMB135
7.35 DP1007_TMB135

What your query is giving me is
COMBO MAX
DP1007_EBZ 57.1 <- Good
DP1007_IPBZ 208 <- Good
DP1007_PBZN 22.7 <- Good
DP1007_TCE 11800 <- Good
DP1007_TMB124 92 <- BAD
DP1007_TMB135 8.58 J <- Good

Allow me to reiterate I do appreciate the correspondence & guidance.

Thanks Again
-eric

 

[PHV]

SELECT A.COMBO, A.Value & A.Qualifier AS [MAX]
FROM yourTable AS A INNER JOIN (
SELECT COMBO, Max([Value]) AS MaxValue FROM yourTable GROUP BY COMBO
) AS B ON A.COMBO=B.COMBO AND A.Value=B.MaxValue;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[efinnen]

Thanks. That last one seemed to work like a charm!