i have one table (Inventory Purchases) that is populated by the user. I want some of these fields to be summarised in a different table (Inventory Database). If done in Excel, it would look like a pivot table (any purchases of the same type are summed together, rather than being treated as a new item).
ie, in the below the highlighted rows i want to show as one row Qty = 9, Cost = $150 within the Inventory Database table
Thanks Darren. Can see theres quite a few threads on this issue (once i knew the terminology to use!). Everything ive found so far though provides solutions to keep the sum/rollup within the same table. Do you know of a solution that will summarise this info into a NEW table?
Create a table and index the rows using the second method described here.
Add enough rows to cover the total number of unique categories
Create a lookup column that targets the Category column in your transactional table. This will give you an array of Categories.
Use a Unique Elements column to remove duplicates from the array
Use a Single Value column, taking the Nth from start of the previous column, where N is the Row Index number. This should give you a list of Categories, one in each row.
Create a multiple relation column, matching each Category with the same column in your transactional table
Create whatever rollups you need via that relation.
