Product Colour Lookup

[mesci]

Hi,

I'm a basic access user, and I have set-up a product management form, however I am stumped on the following section:

I have (outputted from our main stock management system) a field displaying a product reference e.g.:

CEBE KIDS 1772_0043 MATT BLACK
CEBE KIDS 1747_0043 MATT BLACK
KC GATORVILLE_BLGP BLACK
KC SWAMP DENIZEN_9M BLACK
K2003_C3 BLACK/SILVER
K2003_C3 BLACK/SILVER
RAND F/MASTER FM62443_BLACK/VERMILION
RAND F/MASTER FM62446_BLACK/YELLOW
RAND MARINER MR02433PPC_BLACK/COPPER
RAND MARINER MR02424PPC_BLACK/GREY
RAND SSV2433-PPC_MATT BLACK/BROWN
RAND RANGER RX RAD2G99RX_ BLACK
NORVILLE WRAP_BLACK/CRYSTAL
NORVILLE DEMON_BLACK/ROYAL BLUE
GUESS 497_NV

From this (in theory) I would like to cross match against a lookup table, which will identify/extract the "colour" matches.

For example:

CEBE KIDS 1772_0043 MATT BLACK

will match against

MATT BLACK

Firstly is this possible?

However then taking it further, as you can see from the above sample code, some items have multiple colours. Now if required I am happy to spend some time inputting them as variants, so my lookup table has MATT BLACK/BROWN as a variant, but it would be brilliant if I could get the system to possibly look up and return the first value, and any further matching values into seperate text boxes/fields.

So that one product might return

Text Box A: BLACK
Text Box B: [blank]

but the next product might return

Text Box A: BLACK
Text Box B: ROYAL BLUE

and then finally...I need to to return a NULL/"No Defined Colour" value if the record has no match, e.g. for the final record in the example GUESS 497_NV which has no colour.



Is any of this possible?

Many thanks in advance for any help given!

 

[jdraw]

What exactly is Product reference?
Do you have a ProductID, ProductName?
Do you have something like Product Attributes such as size, weight, color etc.?
If these were in separate tables you might have a Junction table (or even 3 or 4)
linking ProductID to ProductColor, ProductID to ProductWeights or some related concept.

Just my 2 cents worth first thing Christmas eve.....
Merry Christmas!!

 

[Simon_MT]

The problem you have is the variable nature of each brand's stock referencing. You will never get consistancy so you are going to need to improvise. You could as suggested create a ProductID and then split out the colourway or colour combination away from the product. You could also consider splitting the Brand off as well. That way you would be able to see all Products by a particular Brand.

Simon

 

[mesci]

Thanks for the responses both! I do have both Product ID and brand, so I'll have a play around with these and see what splitting the field apart gives me, linked into a lookup table.

Thanks.