Associate Number with Each List Box Choice

[beribimba]

Okay, I'll try to explain this as best I can:



I have a table of people (hundreds) with names and other info. I also have a table of foods (hundreds) with info.

Currently, I have a field in the "people" table that is a list box whose source is the "food" names. Multiple values are allowed so I can put checks next to the foods that each person likes. So what I get is that Greg has bananas, apples, and peanuts checked. (Yes, I'm replacing the real data. )

Here's what I want to do now. I would like to associate a number with each food that each person likes (a proportion of the person's diet, say). So I want bananas to have a .6 associated with it, apples have a .3, and peanuts have a .1 (but ONLY for Greg). What this seems to require is a 3-dimensional array, but what Access calls an "array" doesn't seem to be what I want. Any suggestions?

Thanks!

[boblarson]

Is the number that is to be associated with each food going to be different for each person or is (for example) .6 the value of a banana regardless of who chose it?

If it is standard then I would suggest adding that number to a field in the foods table and putting the associated number there. Then you should be able to just use a query to get the numbers for the food that way. Also, I would suggest using a normalized table instead of a multiselect field. It is way harder to query out the data for that than if you just create your own table to store the user's selections. It does make it so you have a little more work for creating a list box and populating the records (like how you would here: http://www.baldyweb.com/MultiselectAppend.htm) but it is going to be much easier in the long run.

[beribimba]

Thanks for the reply. Unfortunately, the number associated with "banana" will be different for each person, so I can't just add it to the food table. And I would just create one field in the people table for each food item except that there are hundreds of food items, so that doesn't seem practical.

[boblarson]

Thanks for the reply. Unfortunately, the number associated with "banana" will be different for each person, so I can't just add it to the food table.

How is it calculated?

And I would just create one field in the people table for each food item except that there are hundreds of food items, so that doesn't seem practical.

You wouldn't create one field for each food item. You use a table to store their choices:

tblPersonsFood
PersonFoodID - Autonumber (PK)
PersonID - Long Integer (FK)
FoodID - Long Integer (FK)
FoodValue - Decimal (scale dependent on number of decimal places)

(the Decimal datatype is more accurate than the single or double which can have floating point rounding errors. To use the Decimal datatype - it is listed separately and not under NUMBER, you select the PRECISION value which is the number of digits it can store total on both sides of the decimal point, up to a total max of 28, and then the SCALE would be for the number of decimal places to store. A 2 would store 2 decimal places.)

[beribimba]

The number isn't calculated, it is input manually.

I'm sorry, I'm not very familiar with Visual Basic, but I think you're giving me the correct solution. Could you please explain the PK and FK? And this is a separate table than the one I currently have, correct? But somehow populated by the current tables?

[boblarson]

Yes, it is a separate table from the one you have now. It would be to replace the field that you currently have (multi-valued field). PK is primary key and FK is foreign key. A foreign key is a primary key in another table. So the PersonID in the Persons table is the Primary Key but when used in the tblPersonsFoods table it is a foreign key.

So, normally you would use a subform to add those and then this new table would be the recordsource for it and the subform would be on the parent form, which in this case should be the form which is bound to the persons table. The FoodID field would use a combo box to select the food and the text box for the value would also be next to it (normally using a datasheet or continuous form format). Then the subform's MASTER/CHILD links would be set to the PersonID field. Access would then populate the PERSONID field for you when you made a selection from the subform of the food.

That would require no coding at all. If you were to use a multiple select listbox then it would be a bit more problematic, especially when trying to fill out the number associated with the selected food.

[beribimba]

Ah ha! This is great. Thanks. I clearly have a lot to learn about databases. I think I get what you're saying and was able to create the form with the subform, but I have a couple more questions:

On the subform, how can I have it show the FoodName associated with the FoodID? I tried adding the FoodName field from the Food table, but it lets me pick different values for these. Basically, I want to choose Banana on the subform instead of 4 (Banana's primary key number).

Also, I tried using the decimal field, but it shows 0 when I actually put a demical in there.

[boblarson]

The row source for the combo box for picking the food, should be something like:

Select * From TableNameHere Order By FoodName

or you can just select the two fields if there are others

Select FoodID, FoodName From TableNameHere Order By FoodName

and then you just set the combo's Column Count property to 2 and then the Column Widths property to 0";2" (or whatever works for you - the zero will hide the ID column and the other width is what the food name column will show).

[beribimba]

Got it! Thanks again! This does exactly what I needed it too.