Sum Query

[cantley1]

Hi, I have been struggling with the concept for weeks, I hope you can help.

I have 2 x input tables (please see attached simplified data sample) 1 x table for transactions and 1 x price table.

In theory I am trying to replicate the last table. (which would be displayed (grouped) on one row) just showing the where a deal has taken place.

I need to sum all the [Price] fields from the price table where it matches the first [Trans_Date] (01/01/2019) from the trans_table and stop when another transaction after the date happens (07/01/2019) and so on for every new [Trans_Date]

shown in red and purple.

[danilang]

Hi @cantley1 

You forgot the attachment.

Dan

[MarqueeCrew · Accepted solution]

@cantley1,

My buddy @danilang must be busy, so I am assisting him in giving you a potential solution.  I fear that this might be a complicated approach, but here's a way to get there.

First I create a record for every transaction date from first to last transaction.  Then setup data for a join to the price table.  Using the running total, I summarize the prices and join that data back.  Finally, I do some cleanup.

If nothing else, this should get you going.

Cheers,

Mark

[cantley1]

Thank you ever so much!! This is fantastic Mark, it's exactly what I needed. I can not thank you enough. Nicky.

[MarqueeCrew]

My pleasure. It was more involved than I initially thought it would be. A little puzzle to warm up with.

Cheers,

Mark