Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9278 > unrolled thread

help with finding highest value

Started bybobh <vulcaned@yahoo.com>
First post2014-04-22 10:03 -0700
Last post2014-05-02 01:33 -0400
Articles 7 — 5 participants

Back to article view | Back to comp.databases.ms-access

Contents

  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

#9278 — help with finding highest value

Frombobh <vulcaned@yahoo.com>
Date2014-04-22 10:03 -0700
Subjecthelp with finding highest value
Message-ID<6c235789-3f8e-4bf3-8007-18acc305e045@googlegroups.com>
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.

[toc] | [next] | [standalone]

#9279

From"Phil" <phil@stantonfamily.co.uk>
Date2014-04-22 19:02 +0100
Message-ID<lj6asi$kq8$1@speranza.aioe.org>
In reply to#9278
On 22/04/2014 18:03:01, 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.
> 

Air code

Function OrderValues()
    
    Dim i As Integer, j As Integer
    Dim Val As Double
    Const NoFields = 3
    Dim MyDb As Database
    Dim Rs As Recordset
    
    Set MyDb = CurrentDb
    Set Rs = MyDb.OpenRecordset("MyTable")
    
    With Rs
        Do Until .EOF
            For i = 1 To NoFields
                If Rs.Fields("Val" & CStr(i)) > Val Then
                    Val = Rs.Fields("Val" & CStr(i))
                    j = i
                End If
            Next i
            .Edit
            !HighestVal = Val
            !WhichVal = j
            .Update
            j = 0
            Val = 0
            .MoveNext
        Loop
        .Close
        Set Rs = Nothing
    End With
    
End Function

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#9280

Frombobh <vulcaned@yahoo.com>
Date2014-04-22 11:56 -0700
Message-ID<aed66e88-b28c-4874-8747-b23990c99d80@googlegroups.com>
In reply to#9279
On Tuesday, April 22, 2014 2:02:56 PM UTC-4, Phil wrote:
> On 22/04/2014 18:03:01, 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. > Air code Function OrderValues() Dim i As Integer, j As Integer Dim Val As Double Const NoFields = 3 Dim MyDb As Database Dim Rs As Recordset Set MyDb = CurrentDb Set Rs = MyDb.OpenRecordset("MyTable") With Rs Do Until .EOF For i = 1 To NoFields If Rs.Fields("Val" & CStr(i)) > Val Then Val = Rs.Fields("Val" & CStr(i)) j = i End If Next i .Edit !HighestVal = Val !WhichVal = j .Update j = 0 Val = 0 .MoveNext Loop .Close Set Rs = Nothing End With End Function Phil --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com

Phil,
just a couple of tweaks, ran it over the 10's of thousands of records in my table and checked several records and perfect.... 
Thank You! Thank You! :) you saved me many hours of work.
bobh.

[toc] | [prev] | [next] | [standalone]

#9281

FromRon Weiner <rw@domain.com>
Date2014-04-22 20:57 -0400
Message-ID<lj736b$egl$1@dont-email.me>
In reply to#9278
bobh was thinking very hard :
> 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.

Would you be i9nterested in a Solution that was contained solely in a 
Query?  If so consider:


UPDATE Table6 INNER JOIN
   (
SELECT Table6.QCode, 
IIf([val1]>[val2],IIf([Val1]>[Val3],[Val1],[val3]),IIf([Val2]>[val3],[Val2],[Val3])) 
AS highestVal, 
IIf([Val1]>[Val2],IIf([Val1]>[Val3],"Val1","Val3"),IIf([Val2]>[Val3],"Val2","Val3")) 
AS WhichVal FROM Table6
   ) AS drvMax
ON Table6.qcode = drvMax.qcode
SET Table6.highestVal = [drvMax].[HighestVal],
Table6.WhichVal = [drvMax].[WhichVal];


Where the name of he Table to be updated is table6. I assumed that the 
Valx and HighestVal columns were Doubles.  If they are text fields you 
will ahve to wrap all of the comparisons in Cdbl() conversions to make 
the compares.

That said while my solution is shorter, it also may turn out to be 
slower than Phils.

Rdub

[toc] | [prev] | [next] | [standalone]

#9306

From"Access Developer" <accdevel@gmail.com>
Date2014-04-29 15:44 -0500
Message-ID<bsah8eF6bu7U1@mid.individual.net>
In reply to#9278
I think you would benefit from one of several relational database design 
books on the market. Your problem is harder than it should be because your 
database does not follow relational design guidelines...

-- 
Larry Linson
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"bobh" <vulcaned@yahoo.com> wrote in message 
news:6c235789-3f8e-4bf3-8007-18acc305e045@googlegroups.com...
> 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.

[toc] | [prev] | [next] | [standalone]

#9313

FromDFS <nospam@dfs.com>
Date2014-04-30 15:00 -0400
Message-ID<ljrh8q$kb$1@dont-email.me>
In reply to#9278
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

[toc] | [prev] | [next] | [standalone]

#9314

FromDFS <nospam@dfs.com>
Date2014-05-02 01:33 -0400
Message-ID<ljvanv$b6p$1@dont-email.me>
In reply to#9313
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);

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web