Summing data for part of a table


#1

Hi all. Sorry for the basic question, but here goes…

I have a base for music. I have a table for songs, a table for customers etc. I have also a table for transactions.

Each line in the transactions table is an individual transaction of a song to a customer including some extra details like cost.

I would like to be able to summarise the total sales figure for an individual song that might have been the subject of multiple transactions. Ideally, my song table would have a field in each row (song) which is the sum of the individual sales for that song from the transactions table. That is, a rollup of only those transactions which meet the criteria of matching the individual song.

I understand that this is not how conditional rollups work. But it might be a common question: for example, a furniture manufacturer with a catalog of 100 items of furniture records the individual sales to 100 customers, but then wants to know how much revenue each item of furniture has created.

Would be very pleased for any suggestions.

Thanks

Steven, Australia


#2

Hi @Steven_Harris

To me this sounds like a case where a normal roll up field is all you need…

I am assuming that each transaction is for a single song and the transaction table is linked to the songs table? If this is the case then you just need a rollup field on Songs which sums the cost field from Transactions.