SUM values from a multi valued lookup field

[johnv]

I have a table that I would like to be able to select multiple values in each record and have it spit out a total. Each item in the lookup is associated with a square footage and if multiple items are selected, I want it to total. From what I gather, multi valued fields are not able to be used in a calculated field due to the back-end nature of them. If you have a suggestion of another way to accomplish this without using a multi valued field, please let me know.

[orange]

Multi-value fields,
lookups at table field level and
calculated fields are all features of MS Access that many developers would advise you to avoid.

As for

If you have a suggestion of another way to accomplish this without using a multi valued field, please let me know.

I suggest your provide readers with a clear statement of your requirement, and some sample data and desired outputs. I'm sure you'll get some constructive ideas.

[davegri]

You need a mainform/subform setup. That's how you associate many objects with one object. The Police Rooms form would be the recordsource for the main form and the Furniture Notes would be recordsource for the subform.
Each police room then could have as many furniture notes associated as necessary.
You need to modify the furniture notes table to include an ID field to point to the proper Police Room record.

Using this technique, you don't deal with the tables directly for any data entry chores.

[johnv]

Using the two images provided above. The intent is that I would like to be able to select the "features" of a room (furniture, casework) . In a separate table I have listed the square footage values of each of these "features". When these "features" are selected, I want the main table to give me a total square footage of the entire selection. So if I selected the 6x6 workstation and the 7x7 workstation, it should tell me the total square footage for this room is 131.

[davegri]

Post your DB with the 2 tables and someone can quickly create the form/subform setup and zip it back to you.