Re: max min question

Jeff wrote:
> Is there a way to achieve this?
>
> In a DB
:-)
Why does nobody ever tell us the type of database they are using? It's
always relevant.

> I have fields named

"fields" - you must be using Access, right? Please don't make us guess.
Also, you likely have a _table_ with these fields. The table resides in the
database.

<snip>
> Let me give example
> say username is TOM
>
> the db would look like this
>
> username rd1 rd2 rd3 rd4 rd5 rd6 total
> TOM 75 69 72 65 68 70 416
>
> so i want a script that would tell me the max is 75 and the min is 65
> Can anyone help here??
> TIA
> Jeff

Your table design is not normalized, making this a much more difficult task
than it has to be. You're working with a database now, not a spread***.
Can you change the design to:

username rd ordinal
TOM 75 1
TOM 69 2
TOM 72 3
TOM 65 4
TOM 68 5
TOM 70 6

The beauty of this design is that if you ever need to add an rd7, you don't
have to change your table design. Which means that you won't need to rwrite
any queries that access this data as well.

You do not need to store the total, since that can be easily calculated in a
query. As easily as the min and max can now be obtained, correct?
SELECT max(rd),min(rd),sum(rd) FROM tablename
WHERE username = 'TOM'

If you cannot change the design for some reason, then you are going to need
to use a query to normalize this data. Create a saved query called
"qNormalizedData" using this sql (it's a union query):

SELECT username, rd1 AS rd,1 AS ordinal
FROM tablename
union all
SELECT username, rd2 AS rd,2 AS ordinal
FROM tablename

union all
SELECT username, rd3 AS rd,3 AS ordinal
FROM tablename

union all
SELECT username, rd4 AS rd,4 AS ordinal
FROM tablename

union all
SELECT username, rd5 AS rd,5 AS ordinal
FROM tablename

union all
SELECT username, rd6 AS rd,6 AS ordinal
FROM tablename

Now, you can use this query to get what you need:
SELECT max(rd),min(rd),sum(rd) FROM qNormalizedData
WHERE username = 'TOM'

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.