Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Posts
    37

    Unanswered: Problem with the 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
    Location
    Toronto, Canada
    Posts
    20,002
    are there any null values in any of the columns involved in the calculations?

    if so, SUM() will exclude them

    doesn't exaplin why the total is twice as much, though

    by the way, you can simplify your WHERE clause:
    Code:
     where TradeHist.[Portfolio Code]
            in ('852','2CM','2CN','2WA')
       and Month([Trade Date])
            in (4,5,6)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    37
    I just checked and all of the fields are populated in the TradeHist table. Something very strange is going on. Any other ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is TradeID unique because it's an autonumber?

    it's still possible you loaded a batch of transactions in twice, and the second batch got new TradeIDs, and when you extracted your stuff to test in excel, you included only rows from the first batch
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Posts
    37
    Hello -
    Thanks for the reply. The trade ID is unique but generated by our trading system. It's not an autonumber genetared by access. I will look into the possibility that some trades were loaded in twice; but it's probably not the case. It's very awarkward because the sum calc is working for lots of trades, then there are random trades where market value calcs are double. Additionally, this is not occurring in any other DB except for the 2002 one. Not sure what is going on. Any other help will be beneficial.

  6. #6
    Join Date
    Sep 2004
    Posts
    37
    OK, I think I have isolated the problem and the trades in question do seem to be entered twice into the database; as previously suggested. So my question now is, what would be the easiest way to delete those lines in the database which have duplicate Trade ID's?

    Reagrds!

  7. #7
    Join Date
    Sep 2004
    Posts
    37
    Additionally, is there a way where I can just view all of those trades that have duplicate Trade IDs before I delete them. This way, I can identify what date periods were entered twice?

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Step 1 - Save duplicate data into a separate table

    select [Trade ID]
    into #temp
    group by [Trade ID]
    having count(*) > 1


    select *
    into DuplicateTrade
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]


    Step 2 - Review the duplicate data

    select *
    from DuplicateTrade
    order by [Trade ID]


    Step 3 - Remove duplicates

    delete from t
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]


    Step 4 - Add back one row of duplicated data from the save table
    insert into TradeHist
    select distinct [Portfolio Code],[Trade ID],[Trade Shares],
    [Order Decision Price],[Trade Execution Price],[Trade Date]
    from DuplicateTrade
    -- this assumes that the duplicate rows are duplicated in their entirety

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by tomh53
    Step 1 - Save duplicate data into a separate table

    select [Trade ID]
    into #temp
    group by [Trade ID]
    having count(*) > 1
    oops ...

    select [Trade ID]
    into #temp
    from TradeHist
    group by [Trade ID]
    having count(*) > 1

  10. #10
    Join Date
    Sep 2004
    Posts
    37
    Tomh53 -

    You are a scholar and a gentleman! Thank you. I'll let you know how this turns out when I complete the steps in a bit.

    Regards!

  11. #11
    Join Date
    Sep 2004
    Posts
    37
    I am getting an error with this statement:
    delete from t
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]


    It is saying there is a syntax error in the FROM clause

    any ideas? And shouldnt I be deleting these from the TradeHist table, not the temp table?

    I also used the below:
    DELETE #temp.* FROM TradeHist, #temp
    WHERE TradeHist.[Trade ID] = #temp.[Trade ID]

    But it said "Could not delete from specified tables"

    Regards!
    Last edited by gissa; 10-18-04 at 16:37.

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    2nd question 1st. yes ... that should be th and not t for the delete statement.

    1st question ... that's what I get for typing without testing.

    This one

    select *
    into DuplicateTrade
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]

    should be

    select th.*
    into DuplicateTrade
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]

    and this one

    delete from t
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]

    should be

    delete from th
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]

  13. #13
    Join Date
    Sep 2004
    Posts
    37
    Quote Originally Posted by tomh53
    and this one

    delete from t
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]

    should be

    delete from th
    from TradeHist th, #temp t
    where th.[Trade ID] = t.[Trade ID]
    Hey Tomh53 -

    I am still getting the"Syntax Error in FROM Clause" error message. Does it matter that I am using Access? Does the delete query need to be re-arranged?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gissa
    Does it matter that I am using Access?
    almost always, yes it does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2004
    Posts
    37
    OK!

    I used this DELETE query in Access and, from an initial test, all the steps seemed to work!

    DELETE DISTINCTROW TradeHist.*, DuplicateTrade.[Trade ID]
    FROM DuplicateTrade INNER JOIN TradeHist ON DuplicateTrade.[Trade ID] = TradeHist.[Trade ID];

Posting Permissions

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