Calculate quantity total for items with kit of parts
I am having trouble creating the right calculations to get the totals I am looking for. My database is set up to specify furniture for commercial interiors projects. The simplified structure is below:
rooms --- roomDetails --- catalog (furniture items) --- typicalDetails (furniture items in a typical) --- catalog2 (catalog TO)
For ease of data entry on large projects we create typicals (kit of parts) of furniture items then enter the typical into a room on the roomDetail layout. We may have other furniture items in that room also.
Example. A typical office may have a desk, task chair, 2 guest chairs and a file. 100 offices may be the same but one needs an extra table. In that case the room would contain the typical and the extra table.
I need to caculate the total number of items (task chairs) for all rooms or the total # of task chairs in the project - (# of typicals * quanity of chairs in the typical) + quantity of chairs in rooms that are not part of a typical. The second part of this equation is simple using a summary field but I can't get the first part to calculate correctly and/or add to the second part. It might just be the context I'm not getting right.
1. In which table should these calcuations occur? RoomDetail or Catalog?
2. Should I create 3 fields...1) total assets in rooms 2) total typicals in rooms * qty per typical 3) sum of 1 & 2
3. Should I use Summary fields and the Get(Summary) function?
4. What should my calculations be?