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 trying to sort these for each ProductID, but I don't think that would help.

Any ideas / pointers would be greatly appreciated!

[jzwp11]

Welcome to the forum!

First, and contrary to your statement that the table is fully normalized -- it is not.

Each product can be made up of up to 3 colours

What you describe is a one-to-many relationship which requires a related table to hold the 3 colors pertinent to a product. Each color would be a record not a field in that table.

tblProducts
-pkProductID primary key, autonumber
-ProductNumber

tblProductColors
-pkProdColorID primary key, autonumber
-fkProductID foreign key to tblProducts
-colorcode
-colorproportion

Now if a color can be used on more than one product, you have another one-to-many relationship. When you have 2 one-to-many relationships between the same two entites (products and colors in this case) you need a junction table. So the question, is each color you mention unique to only 1 product?

[COM34]

Thanks, jzwp11 - I see what you mean. Rookie mistake - each colour is not unique 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

[jzwp11]

Glad to hear that you have a solution for the immediate issue and that you are looking into the table structure further.