Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004

    Unanswered: Problem with Sum function in Access

    Hello All -

    I have a database in Access with the following fields:
    Portfolio Code
    Trade ID <- Uniquie
    Trade Shares
    Order Decision Price
    Trade Execution Price
    Trade Date

    I want to retrieve total traded market value for the 2nd quarter (months 4, 5 and 6) for specific portfolio's. When I retrieve a list of all individual trades (by trade ID which is unique for each), Trade Shares, Order Decision Price and Trade Execution Price and do the Multiplying in Excel (i.e. Order Decision Price * Trade Shares and Trade Execution Price * Trade Shares), the final aggregate total looks correct. However, when I try to do the calculation in Excel and group it so I do not get a list of all trades, just the total Traded Market Value ( using Sum([Order Decision Price]*[Trade Shares]) and Sum([Trade Execution Price]*[Trade Shares]) ) in design view as Expression, the total traded market value is larger than what it was in the first step when the calc was done in Excel. It seems that some of the reported values are exactly double of what I get while doing the calc in Excel.

    This is only happening in the 2002 database. When I use the Sum method in all other databases, the results are 100% on.

    Any ideas??

    Example of the SQL created by design view:
    SELECT Sum([Order Decision Price]*[Trade Shares]) AS Expr1, Sum([Trade Execution Price]*[Trade Shares]) AS Expr2
    FROM TradeHist
    WHERE (((TradeHist.[Portfolio Code])="852" Or (TradeHist.[Portfolio Code])="2CM" Or (TradeHist.[Portfolio Code])="2CN" Or (TradeHist.[Portfolio Code])="2WA") AND ((Month([Trade Date]))=4 Or (Month([Trade Date]))=5 Or (Month([Trade Date]))=6));

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts