Best Practice on Building a Table/Query Holding 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

 

[GinaWhipp]

The best way would be to first adjust your Tables...


tblVehicleModel

• Model Description
• Model Short
• Model Code

tblVehilceColours

• vcID
• vcVehicleModelID (FK relate to tblVehicleModels)
• vcColourID (FK relate to tblColours)

tblColours

• cColourID
• cColour (Black, Blue, ect...)
• cColourTypeID (FK relate to tblColourTypes)

tblColourTypes

• ctColourTypeID
• ctColourType (Interior, Exterior, Veneer)

No you can limit/expand your selection in your Combo Box based on Model selection changing when the Model changes by chaning the RowSource in the After_Update event of the Vehilce Model Combo Box.

 

[philipscodes]

Hi Gina,
Thank you for your detailed guidance.
Worked on your gist and drilled down the tables (See DB Attachment)
Now the whole colour combinations depends first on the variant. Model table is not in use for this purpose.

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 zipped Excel – not complete-this one without Veneers)

Is there an easier, more practical way? I mean there are close to 3000+ combinations!!!

Many Thanks,
Philip

 

[GinaWhipp]

Hmm, got notification you posted something but when I got here no post. I guess I'll it later today as it's almost 4am where I am...

 

[Simon_MT]

This is just a simple explanation.

On the Variants Table there needs to be Model - Lock Down the Variant to a Model.

On the Interior Colour there is already the Variant.

So after inputting the Model and Exterior Colour a dropdown Interior Colour with

[Variant]=combiVariant

That way only the matching Interior Colour schemes are displayed.

Model > Colour > Variant > Interior Colour

Simon

 

[GinaWhipp]

@Simon

Big THANKS because when I looked for the post last night I saw nothing and until you posted today I had not even realized there was a post! OP would have been waiting a longgggg time for an answer from me!

 

[Simon_MT]

Hi Gina

I will try Creating a Form overnight.

Simon

 

[GinaWhipp]

Even better! Thanks Simon

 

[philipscodes]

Hi Simon,

I am not quiet sure if I follow that...
I will give it a try now and update you soon.

Thanks,
Philip

 

[philipscodes]

Hi Simon/Gina,

I've added vehModelID to the variants table to lock it down as suggested.
However, I am not sure what you've meant "On the Interior Colour there is already the Variant."

Plus, I am sorry but I could have saved you time there...

I have now attached the form that I have made.
You might have to update the links to the lookup table (attached earlier)

You could carry on from there if you have not yet started on it...

 

[GinaWhipp]

The Lookup Table posted earlier is missing quite a few tables making it difficult to have a look at anything. Perhaps you could repost? (Or am I missing something?)

 

[philipscodes]

Yes, after I made changes to the lookup table per your directions, I did not update the Bookings_Reservations form...
I had deleted the exterior, interior and veneer separate tables to be replaced by 'vehicle colour' table. The model is to be replaced by variants.

I will send you the updated 2 DBs tomorrow from office.
I am typing this at home now...

 

[GinaWhipp]

No problem... will wait!

 

[philipscodes]

Hi Gina/Simon,

Reattached all required files.

Thanks Friends,
Really appreciate your efforts...

Philip

 

[GinaWhipp]

Okay, have reviewed and step one would be to create your relationships. Once done import the spreadsheets and you can create an APPEND query with the PK's of the fields that need to be appended to *Colour Combinations* and APPEND (add) to said table. So,

1. Create Relationships of tables in database
2. Import spreadsheets with colour combinations
3. Use queries and drop on tables and create Joins to text fields with all appropiate tables and then put the PK's in the fields matching them to your table Colour Combination and APPEND.

Hmm, make a back-up first in case you make a boo-boo...

As a side note, putting spaces in field and table names adds to more typing as now things have to bracketed, i.e.

Your Way = Colour Combinations = [Colour Combinations]
My Way = ColourCombinations = ColourCombinations

I'm lazy so every keystroke I can save is a plus. See...
http://www.access-diva.com/d1.html (bottom of page)

 

[Simon_MT]

Sorry I was called away unexpectedly. Is this the type of think you are trying to acheive.

Simon

 

[GinaWhipp]

@Simon MT...

Did you forget the attachment?

 

[Simon_MT]

Hi Gina

My mistake

Simon