Groups > comp.databases.ms-access > #9314

Re: help with finding highest value

From	DFS <nospam@dfs.com>
Newsgroups	comp.databases.ms-access
Subject	Re: help with finding highest value
Date	2014-05-02 01:33 -0400
Organization	A noiseless patient Spider
Message-ID	<ljvanv$b6p$1@dont-email.me> (permalink)
References	<6c235789-3f8e-4bf3-8007-18acc305e045@googlegroups.com> <ljrh8q$kb$1@dont-email.me>

Show all headers | View raw

On 04/30/2014 03:00 PM, DFS wrote:
> On 4/22/2014 1:03 PM, bobh wrote:
>> Hi All,
>>
>> Anyone got vba they would share to figure out the following;
>>
>> in A2007 I have a table, 6 fields
>> field names are --> QCode, Val1, Val2, Val3, HighestVal, WhichVal
>>
>> for each QCode I need to figure out which of the 3 Val fields has the
>> highest value in it and update the HighestVal field with that value
>> and update WhichVal field with what val field the highest value was
>> in.......
>>
>> so;
>> QCode  Val1  Val2  Val3  HighestVal WhichVal
>> 12398  23.7  54.2  39.0     54.2      Val2
>> 43659  67.8  37.3  78.1     78.1      Val3
>>
>> thanks in advance
>> bobh.
>
> Using your current table structure:
>
> SELECT
> QCode, Val1, Val2, Val3,
> iif([Val1]>= [Val2] and [Val1]>=[Val3],[Val1],iif([Val2]>=[Val3] and
> [Val2] >= [Val1],[Val2],[Val3])) as HighestVal,
> iif([Val1]>= [Val2] and [Val1]>=[Val3],'Val1',iif([Val2]>=[Val3] and
> [Val2] >= [Val1],'Val2','Val3')) as WhichVal
> FROM
> QCodes2;
>
> UPDATE
> QCodes2
> SET
> HighestVal = iif([Val1]>= [Val2] and [Val1]>=[Val3],[Val1],iif([Val2]>=
> [Val3] and [Val2] >= [Val1],[Val2],[Val3])),
> WhichVal = iif([Val1]>= [Val2] and
> [Val1]>=[Val3],'Val1',iif([Val2]>=[Val3] and [Val2] >=
> [Val1],'Val2','Val3'))
>
>
>
>
> But:  I promise... promise... you'll be better served by restructuring
> your db and using queries to view the data.  There's no need to store
> the HighestVal and WhichVal info - you just query for it in real time.
> Plus, if you have more Vals (4,5,6...) you can add rows instead of columns.
>
>
> 'NEW TABLE
> CREATE TABLE QCodes (RowID autoincrement PRIMARY KEY, QCode Long NOT
> NULL, QCodeNum Byte NOT NULL, QCodeValue Double NOT NULL);
>
> 'THIS INDEX WILL KEEP YOU FROM ENTERING DUPES
> CREATE UNIQUE INDEX UIDX_QCODES ON QCodes (QCode, QCodeNum, QCodeValue);
>
> 'ADD SOME DATA
> INSERT INTO QCodes (QCode, QCodeNum, QCodeValue) VALUES (12389, 1, 23.7);
> INSERT INTO QCodes (QCode, QCodeNum, QCodeValue) VALUES (12389, 2, 54.2);
> INSERT INTO QCodes (QCode, QCodeNum, QCodeValue) VALUES (12389, 3, 39.0);
> INSERT INTO QCodes (QCode, QCodeNum, QCodeValue) VALUES (43659, 1, 67.8);
> INSERT INTO QCodes (QCode, QCodeNum, QCodeValue) VALUES (43659, 2, 37.3);
> INSERT INTO QCodes (QCode, QCodeNum, QCodeValue) VALUES (43659, 3, 78.1);
>
> 'SAVE THIS CROSSTAB AS Q_XTAB_CODES
> TRANSFORM First(QCodeValue)
> SELECT QCode, Max(QCodeValue) as HighestVal
> FROM QCodes
> GROUP BY QCode
> PIVOT 'Val ' & QCodeNum;
>
> 'NOW RUN THIS QUERY WHENEVER YOU WANT TO SEE THE DATA
> SELECT Q1.QCode, Q1.[Val 1], Q1.[Val 2], Q1.[Val 3],
> Q1.HighestVal, 'Val ' & T1.QCodeNum as WhichVal
> FROM QCodes T1, Q_XTAB_CODES Q1
> WHERE T1.QCode = Q1.QCode
> AND T1.QCodeValue = Q1.HighestVal


I mean:
CREATE UNIQUE INDEX UIDX_QCODES ON QCodes (QCode, QCodeNum);

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

help with finding highest value bobh <vulcaned@yahoo.com> - 2014-04-22 10:03 -0700
  Re: help with finding highest value "Phil" <phil@stantonfamily.co.uk> - 2014-04-22 19:02 +0100
    Re: help with finding highest value bobh <vulcaned@yahoo.com> - 2014-04-22 11:56 -0700
  Re: help with finding highest value Ron Weiner <rw@domain.com> - 2014-04-22 20:57 -0400
  Re: help with finding highest value "Access Developer" <accdevel@gmail.com> - 2014-04-29 15:44 -0500
  Re: help with finding highest value DFS <nospam@dfs.com> - 2014-04-30 15:00 -0400
    Re: help with finding highest value DFS <nospam@dfs.com> - 2014-05-02 01:33 -0400

csiph-web