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]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23EIxifSRFHA.580@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
>


.