J
Jackson via AccessMonster.com
Hi,
My data is a list of Foreign Exchange positions, each position is actually
showing two lines, so lets say you have sold 1,000,000 EUR vs CHF (buy 1,650,
000 CHF) it will be shown by two records in the table as such:
(another issue is that the COBrate doesn't show the correct rate is one of
the Currencies isn't USD. It will also show the rate of the single CCY line
vs USD if that makes sense. So the goal below is to also come up with a
COBrate for CCYpairs such EUR/CHF = 1.37*1.2 = 1.644
CCYpair CCY Qty COBrate
EUR/CHF EUR -1,000,000 1.37
EUR/CHF CHF 1,650,000 1.2
What I'm wanting to do from totals query is firstly agregated positions onto
CCYpair (only ever be two records per currency pair) showing the Qty of the
leading CCY (EUR in this case) and the correct CCYpair rate which should e 1.
644. So basically, I want to try and get the query to return:
CCYpair Qty COBrate 2ndQty
EUR/CHF -1,000,000 1.644 1,650,000
This seems like it would take custom aggregation formulas (I can usually work
with Sums, Firsts etc) and it's got me stuck. For the Qty I thought maybe I
could use Dlookup where left(CCYpair,3)=CCY but that returned the first
record that matched for all records. The reverse would then simply work
(<>CCY) for the 2ndQty amount. The COBrate I have no idea though how you make
records mulitply in aggregation or if it can be done....any help would be
appreciated!!!
My data is a list of Foreign Exchange positions, each position is actually
showing two lines, so lets say you have sold 1,000,000 EUR vs CHF (buy 1,650,
000 CHF) it will be shown by two records in the table as such:
(another issue is that the COBrate doesn't show the correct rate is one of
the Currencies isn't USD. It will also show the rate of the single CCY line
vs USD if that makes sense. So the goal below is to also come up with a
COBrate for CCYpairs such EUR/CHF = 1.37*1.2 = 1.644
CCYpair CCY Qty COBrate
EUR/CHF EUR -1,000,000 1.37
EUR/CHF CHF 1,650,000 1.2
What I'm wanting to do from totals query is firstly agregated positions onto
CCYpair (only ever be two records per currency pair) showing the Qty of the
leading CCY (EUR in this case) and the correct CCYpair rate which should e 1.
644. So basically, I want to try and get the query to return:
CCYpair Qty COBrate 2ndQty
EUR/CHF -1,000,000 1.644 1,650,000
This seems like it would take custom aggregation formulas (I can usually work
with Sums, Firsts etc) and it's got me stuck. For the Qty I thought maybe I
could use Dlookup where left(CCYpair,3)=CCY but that returned the first
record that matched for all records. The reverse would then simply work
(<>CCY) for the 2ndQty amount. The COBrate I have no idea though how you make
records mulitply in aggregation or if it can be done....any help would be
appreciated!!!