Grouped sum with proportion of total (AC2007)

[AOB]

Hi guys,

I have a table of transactions (close to 1m records) from which I want to query totals by currency and direction (ins and outs) and then also show the proportion of the overall total for each currency. All amounts are absolute (i.e. ins and outs both represented by positive numbers)

To throw some added complexity into the mix, the transaction table records only refer to account (AccountID), which links to a separate account and currency tables.

I can query the sum totals by currency and direction (although I'm not totally happy with the SQL syntax - if anybody can recommend a better way of doing this, I would be extremely grateful, it seems a bit clunky to me!)

SELECT tblCurrencies.CurrencyCode, 
    tmp1.SumPay,
    tmp1.SumPayUSD,
    tmp2.SumRec,
    tmp2.SumRecUSD
 
FROM (tblCurrencies
 
INNER JOIN
 
    (SELECT tblCurrencies.CurrencyCode, 
         Sum(tblTransactions.Amount) AS SumPay, 
         Sum(tblTransactions.USDAmount) AS SumPayUSD
     FROM (tblTransactions
     INNER JOIN tblAccounts
          ON tblTransactions.AccountID = tblAccounts.AccountID)
     INNER JOIN tblCurrencies
          ON tblAccounts.CurrencyID = tblCurrencies.CurrencyID
     WHERE tblTransactions.Direction = "Pay"
     GROUP BY tblCurrencies.CurrencyCode) AS tmp1
 
          ON tblCurrencies.CurrencyCode = tmp1.CurrencyCode)
 
INNER JOIN
 
    (SELECT tblCurrencies.CurrencyCode,
         Sum(tblTransactions.Amount) AS SumRec,
         Sum(tblTransactions.USDAmount) AS SumRecUSD
     FROM (tblTransactions
     INNER JOIN tblAccounts
          ON tblTransactions.AccountID = tblAccounts.AccountID)
     INNER JOIN tblCurrencies
          ON tblAccounts.CurrencyID = tblCurrencies.CurrencyID
     WHERE tblTransactions.Direction = "Rec"
     GROUP BY tblCurrencies.CurrencyCode) AS tmp2 
 
          ON tblCurrencies.CurrencyCode = tmp2.CurrencyCode

I'm struggling to get the proportions per currency though? I tried adding :

((SumPayUSD+SumRecUSD)/Sum(tblTransactions.USDAmount)) AS Proportion

But it tells me that the specified field 'tblTransactions.USDAmount' could refer to more than one table listed in the FROM clause of the SQL statement

Any suggestions?

Thanks!

Al

 

[CJ_London]

The reported error may be misleading - I can't see where you are getting this from

Sum(tblTransactions.USDAmount)

I can't see it in your main query, and not brought through in either of your subqueries

perhaps you need another subquery to calculate this?

 

[AOB]

Sum(tblTransactions.USDAmount) is in both of my subqueries? The subqueries are effectively the same, just different WHERE clauses for pays and receipts (I couldn't think of any other way of doing it, I'm sure this is poor)

But I'm surprised I can't reference it in the main query as well?

And not entirely sure how to put the third subquery together?

 

[AOB]

Boom! Figured it out...

SELECT tblCurrencies.CurrencyCode, 
    tmp1.SumPay, 
    tmp1.SumPayUSD, 
    tmp2.SumRec, 
    tmp2.SumRecUSD, 
    [COLOR=red]((SumPayUSD + SumRecUSD)/(SELECT Sum(tblTransactions.USDAmount) FROM tblTransactions)) AS Proportion[/COLOR]
 
FROM (tblCurrencies....

Completely forgot I was selecting from tblCurrencies and not tblTransactions...

Thanks CJ