AnsweredAssumed Answered

SUM of fields in related records

Question asked by qwedley on Dec 22, 2009
Latest reply on Jan 26, 2010 by qwedley

Title

SUM of fields in related records

Post

Greetings. I have built a rudimentary purchase order database that works pretty well. The only thing I've really struggled with is calculating order totals. I have an item info table, a PO table and a PO Lines table (among others). These tables are all related, if not always directly. I often order from more than one vendor and I often order a given item in more than one package size. So for most items, I have more than one PO line. On top of that, I often have multiple orders for the same item in different years.

 

Here's what I'd like to be able to do:

1) On the PO Lines layout, I'd like to see the running total for the item for the year (if I'm looking at a 2009 PO line, I'd like to see the 2009 total ordered).

 

2) On the Item info layout, I'd like to be able to see the total ordered by year: 2007: 15,200; 2008: 11,000; 2009 12,250; etc.

 

3) On a separate layout (yet to be created), I'd like to see summary information by category and by year: 2007 widgets, nuts, bolts, total--same for 2008 etc.

 

Help! 

Outcomes