Multiple value lists for one field?

[lesterd]

I am working with 10 different schools' voluteer groups. The database is used to track volunteer hours. Each group from the ten locations uses the same form, but the volunteer value list for each location is different.

I want to know if I can use a value list in a certain field. let's call it "where worked" and have the value list change in the drop down box based when I type and assigned number for each one of the 10 locations in a different field.

I guess what I am asking is, if I type a location number "1" in a field called "Location", can I have value list 1 appear in the "where worked" field and if I type in location 2, value list two would populate the drop down box in "Where worked"?

OR do I have to create a table for each group?

Thanks for your help,

Lester

[Ricardo]

Yes you can.

You need to create a realtionship using the Location field (table 1) where you type in '1' etc and relate it to a field in table 2 that matches i.e an other 'Location field'

so relationship:

Location Field (table1) :: Location field (table2).

Now define a value list...and select 'Use Values from field' and specify field...select the realtionship you have created in the drop down menu...then select the field you wish the values to come from i.e 'Where Worked'...then select the radial buton 'include only related values starting from' and select the related table.

Now when you enter 1 in the location field it will only display values in 'Where Worked' from the relating record in table 2 that has a location of 1.

Im not the best at explaining...but it works!!