Best Practice on Building a Table/Query Holding Various Combinations

[philipscodes]

Greetings,

Kindly advise on this issue with tables.
I will be as discrete as I possibly can in this regards...


We are an automobiles organization based in Dubai.
Our main Access DB contains links to a lookup DB(containing many tables).
The main DB has 1 table & 1 form storing information from our sales team on sales orders.

In our lookup DB, below mentioned are 3 table descriptions:
tblVehicleModel

• Model Description
• Model Short
• Model Code

tblExteriorColour

• Colour Code
• Colour Description

tblInteriorColour

• Colour Code
• Colour Description

tblVeneerColour

• Colour Code
• Colour Description

Now in the main DB Form, when user chooses a model (combo box), various exterior, interior and veneer colours corresponding to that model should be queried and available in their corresponding combo boxes.
Sample Data Flow:
Model: LL450 (Chosen)
Exterior Colours: Black (Chosen)
Interior Colours: Black, Blue, Red (Choices)
Veneer Colours:

• If interior is Black then Veneer Black, Blue (Choices)
• If interior is Blue then Veneer Blue, Grey, Pimento (Choices)
• If interior is Red then Veneer Black, Blue, Red (Choices)

There are a total of 12 models, each having similar exterior colours; each exterior colour having various interior colours and depending on the interior
colour chosen, various veneers.

Kindly advise what would be the best table & query methods I should be using to obtain these results on the form.
Thanks,
Philip

[rpeare]

First, all of your color combinations do not need to be in separate tables

you could have

tblColorArea
CA_ID  ColorArea
1      Exterior
2      Interior
3      Veneer

tblColors
Color_ID  ColorDescription
1         Black
2         Red
3         Blue
4         Grey
5         Pimento

Then have a junction table telling you how these are restricted

tblColorCombo
CC_ID  CA_ID  Color_ID1  Color_ID2 
1      1      1          1           'Indicates an EXTERIOR color in CA_ID, ColorID1 is the exterior color, ColorID2 is the interior color
2      1      1          2
3      1      1          3
4      2      1          1          'indicates an INTERIOR color in CA_ID, ColorID1 is the interior color, ColorID2 is the veneer color 
5      2      1          3          'black interior, blue veneer
6      2      3          1          'blue interior, black veneer
7      2      3          3          'blue interior, blue veneer
8      2      3          2          'blue interior, red veneer

This is assuming you want to limit the interior color, based on what the exterior color is, similarly you want to limit the veneer colors based on what interior color is chosen.

if it's built this way then you can easily restrict color choices at each step.

[philipscodes]

Hi rpeare,

Thank you for your detailed guidance.

Worked on your gist and drilled down the tables further as below:

Model

Model
vehModelID	Model Code	Model Description
1	L316	Defender
2	L359	LR2
3	L319	LR4
4	L405	Range Rover Classic
5	L358	Range Rover Evoque
6	L494	Range Rover Sport

Variants

Variants
vehVariantID	Variant Code	Variant Description
25	JCPJ	Defender 110 S/Wagon 2.2D LXV LE
26	JCDJ	Defender 90 P/Up 2.2D
27	JCMJ	Defender 90 S/Wagon 2.2D
28	IUVJ	LR2 2.0 I4 HSE
29	GDVH	LR4 3.0 SC HSE
30	GPVH	LR4 5.0 V8 HSE LE
31	RCBV	Range Rover 5.0 SC Autobiography
32	RBBV	Range Rover 5.0 HSE
33	PUBV	Range Rover Evoque 2.0 Dynamic
34	PUBT	Range Rover Evoque 2.0 Dynamic Coupe
35	SDBV	Range Rover Sport 3.0 SC HSE
36	SCBV	Range Rover Sport 5.0 SC Autobiography Dynamic

Colour Types

Colour Types
colourTypeID	Colour Type
1	Exterior
2	Trim
3	Veneer

Vehicle Colours (Only Few Shown)

Vehicle Colours
vehColourID	Colour	Colour Code	colourTypeID
1	Grey Cloth Seat	T	2
2	Ebony / Ebony Lthr Sts	TCB	2
3	Ebony	TDT	2
4	Amond	TFA	2
5	Almond / Arabica Lthr Sts	TGU	2
96	Bering Grey (Autobiography)	853	1
97	Mariana Black	860	1
98	Barolo Black	861	1
99	Indus Silver	863	1
100	Mauritius Blue	864	1
101	Havana	865	1
102	Aintree Green	866	1
103	Fuji White	867	1
104	Firenze Red	868	1
105	Luxor	869	1
118	Figured Macassar	XXXX	3
119	Grand Black Lacquer	088EH	3
120	Ebony Macassar	088GF	3
121	Grey Oak Wood	088GM	3
122	Shadow Zebrano	088HB	3
123	Shadow Walnut	088HF	3
124	Micro Mesh Aluminium	088HQ	3
125	Grand Black	088HS	3

Finally, the table in question…

Colour Combination

Colour Combination
vehColourCombID	vehVariantID	vehColourID_Ext	vehColourID_Int	vehColourID_Ven
2881	1	2	3	4

My current issue is how do I go about entering data into the colour combination table.
I have been given a restricted colour combination table from our clients. (Attached Excel – not complete. This one without Veneers)

Is there an easier way? I mean there is close to 3000+ combinations!!!

Many Thanks,
Philip

[rpeare]

I had assumed based on your original post there was a limited list of interiors that could go with any exterior color, and likewise, there was a limited list of veneers for each interior color, if that is NOT the case, in other words a client can have any possible color combination you don't need the color combination table at all.

If there ARE very specific combinations you might be able to set up something to cycle through items in a list and create the records for you but it would be pretty useless after you have your database up and running because you'd only be adding 'new' color combinations. You *might* be able to parse that excel file to populate the table but then you'd be missing interior > veneers. but honestly you're talking about data entry that is 3 fields, exterior, interior, veneer which is very easy to do, especially if your color combinations can be limited by manufacturer.

[philipscodes]

We have 12 vehicle variants and a total of 137 colours(inclusive of exterior/interior/veneers). So that alone would give a total of 419220 combinations.
Out of which our clients have restricted to about 3000 odd combinations in an excel file.

I could try replacing(Ctrl+H) the words in excel with the corresponding ID numbers and then append these over to the combination table.... What say?

[rpeare]

if you've typed the colors exactly you should be able to parse the information (based on your example excel file) from the excel file with some VBA without having to manually do anything. If there are subtle differences in spelling/spacing then it could be more cumbersome.

[philipscodes]

sure will do and thanks...
Will update you soon...