Sum of line items by material group
I have a database consisting of a materials, line items, invoices and contacts table. I display and enter my line items per invoice in a portal in my invoices table. My materials table is grouped by sort of material, eg steel, timber etc.
In my invoices table I have a field that totals all line items in an invoice using the Sum function. What I would also like is fields that total the line items per invoice that belong to the same material group eg total amount for timber, steel etc.
Any suggestions would be great!