Sum Query

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.

Example.xlsx

Macros

Date Time

Accepted answers

MarqueeCrew

@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

Sum Query.yxmd

All comments

danilang

Hi @cantley1

You forgot the attachment.

Dan

MarqueeCrew

@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.

If nothing else, this should get you going.

Cheers,

Mark

Sum Query.yxmd

cantley1

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703