Adding Subtotals to a Table or Query

[wojosh6]

Hi Folks,



First time posting here. I have a massive (850K records) table that is setup somewhat similar to the table below. What I am attempting to do in the "%Split" column is find that specific line item's percentage makeup of the total counterparty. For example, within the Jackson counterparty we have three CUSIPs that total 200. I know that abc123 makes up 12.5% of the counterparty total, but I can't quite figure out the code or design view setup to perform this calculation. I get stuck trying to find a subtotal for each counterparty grouping.

Any help is greatly appreciated!

Counterparty	CUSIP	Amount	%Split
Jackson	abc123	25
Jackson	abc789	50
Jackson	abc456	125
Johnson	abc123	50
Johnson	abc999	50
Johnson	abcefg	100
Mellon	qqq123	200
Mellon	abc123	150

[John_G]

The approach I would use would be to make a separate summation query to get the totals for each counterparty, then link that query to the one above on the counterparty ID. You would then use the sum values from that second query to calculate the percentages.

[June7]

Consider building a report using its Sorting & Grouping features with aggregate calcs in group and report footers.

[CJ_London]

or this query will work, but with 850k records, ensure you have indexed counterparty

SELECT *, amount/(SELECT Sum(Amount) FROM myTable WHERE counterparty=mytable.counterparty) AS PCSplit
FROM myTable

Note you should not use non alphanumeric characters (e.g. %) in table and field names

[wojosh6]

Thanks for the reply! I created a query using grouping (pretty much the equivalent of a pivot table) by counterparty & linked that back to a second query where I actually perform the calculation. Everything works great. Thanks!!

The approach I would use would be to make a separate summation query to get the totals for each counterparty, then link that query to the one above on the counterparty ID. You would then use the sum values from that second query to calculate the percentages.