max min question

Is there a way to achieve this?

In a DB I have fields named
username
rd1
rd2
rd3
rd4
rd5
rd6
total

each rd is a score that is entered from form going to an asp page. that asp
page updates the total.

I would like to be able to get a max and min for each username. i know how
to geta max or min of a field, but with this, i want to be able to get it
across the row..not down the column. short of using array's..is this
possible?
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

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.


> 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 spreadsheet.
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.

Re: max min question

"Jeff" wrote in message
news:fJSdnZMja44S7PjfRVn-vQ@adelphia.com...
> Is there a way to achieve this?
>
> In a DB I have fields named
> username
> rd1
> rd2
> rd3
> rd4
> rd5
> rd6
> total
>
> each rd is a score that is entered from form going to an asp page. that
> asp page updates the total.
>
> I would like to be able to get a max and min for each username. i know how
> to geta max or min of a field, but with this, i want to be able to get it
> across the row..not down the column. short of using array's..is this
> possible?
> 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
>
Please provide database, version and DDL:
http://aspfaq.com/etiquette.asp?id=5006
http://aspfaq.com/etiquette.asp?id=5009

In the absence of that information all I can provide is narrative. Create a
union query to transform the columns into rows, then perform your aggregate
MIN/MAX functions on the resulting query.

Re: max min question

Yes Bob, my apologies, it is Access DB. I see where you are going with that.
Instead of one row for each username, there would be 6 for each, thus making
the scores all in the same field.
Sorry for my lack of information
Thanks a bunch.
jeff

"Bob Barrows [MVP]" wrote in message
news:%23EIxifSRFHA.580@TK2MSFTNGP15.phx.gbl...
> 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.
>
>
>> 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 spreadsheet.
> 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.
>
>