[gissa]

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));

[r937]

http://www.dbforums.com/t1045446.html