Option list from one table

[amanxman]

Hi,

I have the following say;

tblProducts:
Name
Categories
Price

tblCategories:
Name
IDNumber

at the moment, no relationships built

tblProducts "categories" requires the IDNumbers of the categories in tblCategories - there are mutiple IDNumbers for each product, and they need to be seperated by a comma - for example ProductA has categoryIDs 1,3,6 (where categories 1,3,6 and called A,C,F)

At the moment, I am having to manually look up what each categoryID number is by CategoryName, and then type it in the tblProducts category field.

I want to have a form which provides the tblProduct "Name" and then a long list of all the tblCategory "Name" contents as option (or tick) boxes. So all cateory Names listed.

Then when I tick mutiple boxes on this form for the categories, it populates the tblProduct "Categories" field with the tblCategories "IDNumber" codes, with a comma between each one! (so the names chosen actually populates with the ID number)

So basically if on my form, ProductA has category A,C,F ticked -- so the field tblProduct "Categories" is "1,3,6" (where 1,3,6 are the category IDNumbers and A,C,F are category names)

In other words, I want to be able to choose the categories for my products by choosing the Category Name (rather than ID) but having the ID populated into the relevant field -- I can do this with a combo box displaying a different field to that thats populated....but this only allows one selection....

I want mutiple categories to be chosen (via the form with option/tix boxes), and thus to populate the relevant field with commas seperating.

Is this last bit possible?!!

How!

thanks in advance
rob

 

[MajP]

Anything is possible, but this is a bad, bad idea. You are creating a non-normalized table. You never want to put multiple relations into a single field. You want to set up a many to many relationship by adding a third table.

joinTblProduct_Categories
intproductID (FK)
strCategoryID (FK)

tblProducts
autoProductID (PK)
Name
Price

tblCategories
CategoryID (PK)
Name
Build a main form with the products table.
Now you can build a subform using the join table linked to the categories table, and you can add all the categories you want with a pulldown.

 

[amanxman]

Thanks very much for your reply

Hi, thought about doing it with a mid-tbl as this will populate the middle table with individual results.

However I still need the categories field in the Products table to be in the format categoryID,categoryID,categoryID (ie. each categoryID seperated with a comma) -- I know this isnt the best way to do it, but it is the required format for the final database as it needs to be uploaded to the web (for an online shop) in this format.

So how would I populate the tblProducts "Categories" with ID,ID,ID etc?

Thanks again in advance
rob

 

[PHV]

Have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Final format could be whatever you want. With a normalized table you could then build a query that uses a function to concatenate the data into this format. There are several FAQs on how to do it. Now you have the best of both worlds. I never let final format of products dictate table design.

That said. If you want to just stick these values into a table with commas the most flexible choice would be a multiselect combo box not bound to your control. After you highlight your choice fire an event that reads the items selected, builds a string, strips off any extra commas, and populates the field.

Less flexible is to have a bunch of tick boxes or radio buttons. Can not be in an option group cause you want to choose multiple selections. On the forms before update, read the controls, build the string, strip off an extra commad, populate the field. Less flexible because if you add a category you need to redesign the form.

What one you want?

 

[MajP]

I did not see PHV's post while I was typing, but that is the idea of concatenating normalized data which I refer to as well.