Multiple Dropdowns Issue

[tommy83016]

Hi Guys
We love Dynamic Dropdowns and have been using the plugin for many years.

We have hit a stumbling block using DD when we want to go to 3 or more levels.

Level 1 is manufacturer, Level 2 Model, no problems there.
Level 3 is size. Ideally, we would like to have a table in the Access Database that contains 1 entry for each possible size available to all Models.

Is there a way to pull into the 3rd drop down list the avaialble sizes for that model, without having to have a huge table without having to repeat sizes over and over again for the different models using the ModelID?

To explain better I have attached the DB. The URL can be found here >> http://www.chainsawchain.eu

Many Thanks in Advance

[Jason Byrnes]

you would need to create a linking table to link the available sizes to the models

modelSizes:
modelSizeID - Primary key Column
modelSizeSizeID - Foreign key column, relates to size.sizeID
modelSizeModelID - Foreign key column, relates to model.modelID


the recordset to populate the sizes Dynamic array will be a join query:

SELECT modelSizes.modelSizeModelID, Size.sizeID, Size.SizeName
FROM Sizes
INNER JOIN modelSizes
ON modelSizes.modelSizeSizeID = Size.sizeID


Then in the populate list from array, set the modelSizes.modelSizeModelID as the parent ID, Size.sizeID as the Child ID and Size.SizeName as the child label

[tommy83016]

Thanks for replying Jason!

You lost me a little (actually a lot).

I create a new table called 'modelSizes' with the columns
modelSizeID - Primary key Column
modelSizeSizeID - Foreign key column, relates to size.sizeID
modelSizeModelID - Foreign key column, relates to model.modelID

What data do I put in these columns?

In the recordset where does Size.sizeID, Size.SizeName come from?

THen, where does 'FROM Sizes' come from. Do i need to create a table called Sizes?

Is itpossible you could provide an example form my database attachment I could build upon? Willing to pay if required. But, if I need to pay, I would like to have four columns where the 4th would be the price based on chain length.

Many Thanks for your assistance

[Jason Byrnes]

I am working on a Macintosh and cannot open Microsoft Access files. I cannot give you specifics matching the column names exactly, but the Idea here is that you are creating a linking table to relate the sizes in the Size table to the models in the model table.

the linking table will use the primary key columns from the model and size tables to create the relationships.

in your model table you have:
modelID | ModelName
1 | KES35


lets say the size table has the following sizes:
SizeID | SizeName
1 | 12" (30CM)
2 | 14" (35 CM)
3 | 16" (40CM)


the records in the modelSizes table to create the link would look like:
modelSizeID | modelSizeSizeID | modelSizeModelID
1 | 1 | 1
2 | 2 | 1
3 | 3 | 1



In the recordset where does Size.sizeID, Size.SizeName come from?


these are the columns you already have in your size table



Then, where does 'FROM Sizes' come from. Do i need to create a table called Sizes?



no this is your existing size table. Like I say, I cannot open the Access database, so cannot give exact examples for your database structure.


if you wish to get one on one help creating this, you could purchase a premiere ticket, see the support landing page for details:
http://www.webassist.com/support/

[tommy83016]

Hi Jason,

Thanks for the reply.

I too am on a mac and have to use someone elses pc to work the access database.

In your opinion, would it be better to work with asp and access or php and mysql for my requirements for this project. I am expecting the site to become popular (hopefully).

Tommy

[Jason Byrnes]

if you are expecting to get a lot of simultaneous traffic on the site, PHP / MySQL is a better way to go.

in general, I would suggest PHP / MySQL as our support for ASP is being phased out.

[Dave Buchholz]

Tommy,

can I suggest you have a look at the tutorial I wrote some time ago on Dynamic Dropdowns here (http://www.i-cre8.co.uk/webassist/multiple-dynamic-dropdowns/) which has a sample database along the lines of what Jason is explaining to you.

Hopefully this will make things clearer to you.

I am on a mac also so I can't open your MS Access file