Selecting a row's highest option and its associated code

[COM34]

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       
1           Col034         100                                                                                      100       
2           Col023         25                   Col241        50                  Col065        25                  50       
3           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 trring to sort these for each ProductID, but I don't think that would help.

Any ideas / pointers would be greatly appreciated!

 

[plog]

This is a fully normalised table.

Incorrect. This data should be in 2 tables, Products and Colors. With that structure, this issue is simple. This is how your data should be structured:

Products
ProductID
1
2
3

Colors
ProductID, Color, Proportion
1, Col034, 100
2, Col023, 25
2, Col241, 50
2, Col065, 25
3, Col024, 17
3, Col211, 23
3, Col360, 60

Get your data there and the query to get the color with the highest percentage is easy.

 

[COM34]

Thanks, plog - I see what you mean. Rookie mistake - each colour is not unique to a product so this would involve a 1-m relationship.

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 a read-only 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, someone has provided me with a query that identifies the highest apportioned code based on this one table - even though it takes a while run on 10k plus records, it is better than nothing. Hopefully I'll find a normalised set in Oracle!

Thanks for your input

 

[gemma-the-husky]

what you are asking for is not easy though.

unfortunately, a dmax query will not let you include (eg as expression) other data assocaited with the row that generated the dmax

so even, given normalised data


colourway, colour, percentage
1, red, 50
1, blue, 25
1, green, 25

a dmax query identifies 50, but does not also give red, without a second read (to find the colour associated with the value of 50)

one way round this is to "select top 1" of a sorted query instead of using dmax

it is also difficult to get the top values of all the different colour groupings, at the same time

it's worth trying different query options to see how easily you can get at what you want.

-----
if you cannot use properly normalised data, then you may need a function to determine the value in each row

sort of this functionality

maxval=0
maxcolour=""
for each colour
    if colourval>max then 
        max=colourval
        maxcolour = thiscolour
    end
next

out of interest - can there only be 3 colours in the mix (ie is this a standard RGB pallette)

if so, then maybe the colour can be represented more normally as a single colour (with each of RGB settings represented by a number in the range 0-255) - in which case maybe there are standard functions libraries out there to manipulate such colour values.

it is easy enough to change a RGB value from a percentage to a 255 value. (just muliply by 2.55!)