Max and Min Numbers

[labprof]

In my table in have 6 fields named: C1PR2, C2PR2,C3PR2,C4PR2,C5PR2, AND C6PR2.

Each field gets a number data entry from my form.

I have a query with each field in the query grid and another 2 fields where I need to show the highest number (MAX) and the lowest number (MIN) in the fields and also the deta between the two.

Can someone help me the understand what to write in the fields to return these numbers.

Your help would be greatly appreciated.

Labprof

 

[Golom]

Using just one of your fields as an example

Select C1PR2,
       (Select MAX(C1PR2) From tbl) As MaxC1PR2,
       (Select MIN(C1PR2) From tbl) As MinC1PR2

From tbl

Of Course

Select C1PR2
Order by C1PR2

will display them in ascending order with the MIN first and the MAX last.

 

[labprof]

Golom,

Thank you ever so much for your reply. But perhaps I wasn't clear enough. Each field (C1PR2, C2PR2,C3PR2,C4PR2,C5PR2, AND C6PR2.) is part of a unique record. So in other words each record has all 6 fields.
In my query I need to be able to find the highest number in the 6 fields and the lowest number in the 6 fields and then calculate the difference. So I can display on a form the Min , the Max, and the Delta.

Can I do this in a query?

Thank you for your help.

Labprof

 

[lespaul]

the reason that you are finding this so difficult to accomplish is because your table isn't normalized. If you are still at a point that the table can be modified you may want to consider that option because in the long run it will be easier to maintain and modify. See the link below for more information on normalization.

A UNION query can mimic normalization:

SELECT IDField, "C1PR2", C1PR2 FROM TableName
UNION
SELECT IDField, "C2PR2", C2PR2 FROM TableName
UNION
SELECT IDField, "C3PR2", C3PR2 FROM TableName

then maybe you can join into that query to get the min and max for each record.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[labprof]

Leslie,

I don't understand. Each record requires that I have data for each of these fields. What do you mean by normalization?
Each record is for a particular lab analysis that my lab performs. And any record/analysis could have different results. So I need to be able to find the highest number
and the lowest number from the 6 fields and display them on a form (using a subform) from a query. And also the delta between the two numbers (highest and lowest).
How would I normalize this data?

Your help is appreciated.

Labprof

 

[lespaul]

It looks like you have a One-To-Many relationship: There are many C#PR2 entries for each lab record.

This would be normalized by having a table that for each lab record contains the many pieces of information.

Think of it this way. Say a student has 5 classes they take for a program. If we set it up like you have it would be:
[tt]
StudentID Class1 Class2 Class3 Class4 Class5
1 Math English Spanish Science Computer Lab
2 English Math French Computer Lab Science
[/tt]

a normalized approach would store this as:

[tt]
StudentTable
StudentID
Name
Address
(anything else about the STUDENT)

StudentID Name Address
1
2


StudentCourses
StudentID
CourseNumber
CourseName

StudentID CourseSection CourseName
1 1 Math
1 2 English
1 3 Spanish
1 4 Science
1 5 Computer Lab
2 1 English
2 2 Math
2 3 French
2 4 Computer Lab
2 5 Science
[/tt]

Did you read the 'Fundamentals' document? It explains really well what normalization is and some of the basic steps to determine what tables you need.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

Leslie has done her usual excellent job of summarizing what normalization is about. Now, applying all that to your situation ...

The code to create a normalized version of your data may look something like this

SELECT AnalysisID, '1' As FieldID, C1PR2 As CPRValue FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '2', C2PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '3', C3PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '4', C4PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '5', C5PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '6', C6PR2 FROM tblAnalyses

Let's call that "qryN" (for normalized), then

Select Min(CPRValue) As MinCPR, Max(CPRValue) As MaxCPR,
       (Max(CPRValue)  - Min(CPRValue)) As [Difference]
From qryN
Where AnalysisID = 123

Returns the min, max and difference values for AnalysisID = 123
You could of course do it all in-line with

Select Min(CPRValue) As MinCPR, Max(CPRValue) As MaxCPR,
       (Max(CPRValue)  - Min(CPRValue)) As [Difference]
From 

(
SELECT AnalysisID, '1' As FieldID, C1PR2 As CPRValue FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '2', C2PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '3', C3PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '4', C4PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '5', C5PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '6', C6PR2 FROM tblAnalyses
) As X

For AnalysisID = 123

And finally, if you want the results for every AnalysisID in the table then

Select AnalysisID, Min(CPRValue) As MinCPR, Max(CPRValue) As MaxCPR,
       (Max(CPRValue)  - Min(CPRValue)) As [Difference]
From 

(
SELECT AnalysisID, '1' As FieldID, C1PR2 As CPRValue FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '2', C2PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '3', C3PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '4', C4PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '5', C5PR2 FROM tblAnalyses
UNION ALL
SELECT AnalysisID, '6', C6PR2 FROM tblAnalyses

) As X

Group By AnalysisID

Order By AnalysisID

 

[PHV]

to reply the original question, create the following functions in a standard code module:

'A generic function to get the min value of an arbirtrary numbers of same type values:
Public Function MyMin(ParamArray Args())
Dim i As Long, rv
If UBound(Args) >= 0 Then rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
  If Trim(rv & "") = "" Or (rv > Args(i) And Trim(Args(i) & "") <> "") Then rv = Args(i)
Next
MyMin = rv
End Function
'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

And now your query:
SELECT *
, myMin(C1PR2,C2PR2,C3PR2,C4PR2,C5PR2,C6PR2) As theMin
, myMax(C1PR2,C2PR2,C3PR2,C4PR2,C5PR2,C6PR2) As theMax
FROM yourTable

Anyway, have a look here:

http://r937.com/relational.html

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