SUM of fields in related records
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.