AnsweredAssumed Answered

Calculate quantity total for items with kit of parts

Question asked by ssp.columbus on May 3, 2012
Latest reply on May 9, 2012 by philmodjunk


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?