UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Selecting A Row's Highest Option And Its Associated Code, Office 2010    
 
   
COM34
post Apr 4 2012, 09:48 AM
Post #1

New Member
Posts: 6
I have an MS Access table that contains colour codes and their relevant percentage with reference to particular products. Each product can be made up of up to 3 colours and I am trying to determine the most used colour for each product - so far I can determine the percentage of the highest colour (using a MaxOfList VBA module), but am unable to associate it with the actual colour.

Table example:
ProductID ColourCode1 Colour1Proportion ColourCode2 Colour2Proportion ColourCode3 Colour3Proportion Highest
001 Col034 100 100
002 Col023 25 Col241 50 Col065 25 50
003 Col024 17 Col211 23 Col360 60 60

So as an example, product 001 would return Col034, product 002 would return Col241 and product 003 would return Col360.

The colour codes are as they stand i.e. they do not refer to another table to return a 'real-world' colour, and there isn't a rule on the order by which the the codes should be entered. This is a fully normalised table.

Essentially I need something that performs a Vlookup on the Highest column, and returns the value which is 1 field to the left of the result. Unfortunately a dlookup won't help me here.

I've toyed with the idea of creating temporary tables for each listed block of colour-related columns (e.g. ColourCode1 and ColourProportion1 in one table, ColourCode2 and ColourProportion2 in another etc) and trying to sort these for each ProductID, but I don't think that would help.

Any ideas / pointers would be greatly appreciated!

This post has been edited by COM34: Apr 4 2012, 10:39 AM
Go to the top of the page
 
+
theDBguy
post Apr 4 2012, 09:52 AM
Post #2

Access Wiki and Forums Moderator
Posts: 48,114
From: SoCal, USA
Hi,

(IMG:style_emoticons/default/welcome2UA.gif)

QUOTE (COM34 @ Apr 4 2012, 07:48 AM) *
...This is a fully normalised table.

Are you working with a crosstab query? If not, that sample data doesn't look like it is properly normalised because of the repeating groups of colour fields.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
arnelgp
post Apr 4 2012, 10:00 AM
Post #3

UtterAccess Ruler
Posts: 1,090
Can you show us your MaxOfList VBA module, maybe from there we can stitch things.
Go to the top of the page
 
+
COM34
post Apr 4 2012, 10:00 AM
Post #4

New Member
Posts: 6
thanks for a very fast response, theDBguy (IMG:style_emoticons/default/smile.gif)

No, I'm not working with a crosstab query and as far as I can see it is fully normalised (happy to be proved wrong though!) as the proportion is not dependant on the code - they are 2 seperate entities as the proportion can take any value from 0 to 100 to 2 d.p.
Go to the top of the page
 
+
COM34
post Apr 4 2012, 10:05 AM
Post #5

New Member
Posts: 6
Hi anelgp (another really fast response - am seriously impressed here (IMG:style_emoticons/default/smile.gif) )

The code I use is
CODE
Function MaxOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
End Function


which I then call up in a Select query
Go to the top of the page
 
+
theDBguy
post Apr 4 2012, 10:16 AM
Post #6

Access Wiki and Forums Moderator
Posts: 48,114
From: SoCal, USA
Hi,

QUOTE (COM34 @ Apr 4 2012, 08:00 AM) *
thanks for a very fast response, theDBguy (IMG:style_emoticons/default/smile.gif)

No, I'm not working with a crosstab query and as far as I can see it is fully normalised (happy to be proved wrong though!) as the proportion is not dependant on the code - they are 2 seperate entities as the proportion can take any value from 0 to 100 to 2 d.p.

Thanks for your reply. The first rule of nomalisation is to not have any repeating groups, and having separate columns for different colours and proportions violates that rule.

If, instead, you store your data as follows:

CODE
ProductID  ColourCode  Proportion
001        Col034      100
002        Col023      25
002        Col241      50
etc...

Then, you can simply use a Totals query to get the Max() value.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
COM34
post Apr 4 2012, 10:47 AM
Post #7

New Member
Posts: 6
Ah, I see. Will try this - the data I have is via an ODBC link to an Oracle database, so I don't have access to the backend, so I'll see what I can do. It's definitely a step in teh right direction anyway, thanks (IMG:style_emoticons/default/smile.gif)
Go to the top of the page
 
+
arnelgp
post Apr 4 2012, 11:19 AM
Post #8

UtterAccess Ruler
Posts: 1,090
This is not very neat, but you can forget about your function:



CODE
SELECT T1.ProductID,
    T1.ColourCode1,
    T1.Colour1Proportion,
    T1.ColourCode2,
    T1.Colour2Proportion,
    T1.ColourCode3,
    T1.Colour3Proportion,
    (SELECT TOP 1 COLORCODE FROM
        (SELECT tblColourProportion.ProductID,
            tblColourProportion.ColourCode1 As ColorCode,
            tblColourProportion.Colour1Proportion As ColorProportion
            FROM tblColourProportion
            UNION
            SELECT tblColourProportion.ProductID,
            tblColourProportion.ColourCode2 As ColorCode,
            tblColourProportion.Colour2Proportion As ColorProportion
            FROM tblColourProportion
            UNION
            SELECT
            tblColourProportion.ProductID,
            tblColourProportion.ColourCode3 As ColorCode,
            tblColourProportion.Colour3Proportion As ColorProportion
            FROM tblColourProportion
        ORDER BY PRODUCTID, COLORPROPORTION DESC) AS T_TEMP WHERE T_TEMP.PRODUCTID=T1.PRODUCTID) AS Colour,
    (SELECT TOP 1 ColorProportion FROM
        (SELECT tblColourProportion.ProductID,
            tblColourProportion.ColourCode1 As ColorCode,
            tblColourProportion.Colour1Proportion As ColorProportion
            FROM tblColourProportion
            UNION
            SELECT tblColourProportion.ProductID,
            tblColourProportion.ColourCode2 As ColorCode,
            tblColourProportion.Colour2Proportion As ColorProportion
            FROM tblColourProportion
            UNION SELECT
            tblColourProportion.ProductID,
            tblColourProportion.ColourCode3 As ColorCode,
            tblColourProportion.Colour3Proportion As ColorProportion
            FROM tblColourProportion
        ORDER BY PRODUCTID, COLORPROPORTION DESC) AS T_TEMP WHERE T_TEMP.PRODUCTID=T1.PRODUCTID) As ColourProportion
FROM tblColourProportion AS T1;


You need to replace tblColourProportion with the actual table name you have.

This post has been edited by arnelgp: Apr 4 2012, 11:20 AM
Go to the top of the page
 
+
COM34
post Apr 5 2012, 07:22 AM
Post #9

New Member
Posts: 6
Fantastic, thanks a lot arnelgp - this is of great help.

I have since discovered that this table is actually a derived table (I'm not sure if this is the correct terminology - it definitely isn't a base table anyway) - this data comes in via an ODBC link from an Oracle database. Will have a look through all the other tables in the hope that there is a normalised set somewhere.

In the meantime, this code will be great!

Thank you very much (IMG:style_emoticons/default/smile.gif)
Go to the top of the page
 
+
arnelgp
post Apr 5 2012, 08:22 AM
Post #10

UtterAccess Ruler
Posts: 1,090
Yes it would be easier for you when your tables are normalized (don't over normalize, its bad too).
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 25th May 2013 - 11:33 PM