Results 1 to 7 of 7
  1. #1
    S2000magician is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    31

    Having trouble getting a query to sort properly

    I have a table with portfolio transactions: which portfolio, the date of the transaction, which securities were bought or sold, and how much of each security was bought or sold.

    I want to create a query that will give me the transactions for a specific portfolio. For each transaction date (AsOfDate), I would like the data sorted by the amount bought or sold (PMV). The amount bought or sold is a number data type (e.g., -0.12 if 12% were sold, or +0.035 if 3.5% were purchased).

    In my query I have: ORDER BY AsOfDate, PMV.

    The result that I get is sorted correctly by the transaction date, but not by the amount bought/sold; the order of the amount bought/sold in each transaction seems random.



    Obviously, I'm doing something wrong. What should I be doing so that it's right?

    Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post the SQL of the query you are using.

  3. #3
    S2000magician is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    31
    Quote Originally Posted by orange View Post
    Please post the SQL of the query you are using.
    Here you go:

    SELECT * FROM Models WHERE Models.ModelID = "XYZ" ORDER BY AsOfDate, PMV

    Thanks!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I mocked up a table to simulate your set up.


    Table Values
    id portfolio asofdate pmv
    1 abc 01-Jul-99 0.2
    2 abc 03-Jul-99 0.31
    3 abc 09-Aug-99 -0.09
    4 abc 21-Sep-99 0.19
    5 abc 29-Oct-99 -0.4
    6 abc 21-Sep-99 -0.06


    Query sql
    Code:
    SELECT PortfolioTrans.portfolio, PortfolioTrans.asofdate,Round( (PortfolioTrans.pmv *100),2) as trans,
    iif(trans >0, "Bought","Sold") as TransType
    FROM PortfolioTrans
    WHERE (((PortfolioTrans.asofdate) Between #1/1/1999# And #31-dec-99#))
    ORDER By asofdate,pmv
    Result

    portfolio asofdate trans TransType
    abc 01-Jul-99 20 Bought
    abc 03-Jul-99 31 Bought
    abc 09-Aug-99 -9 Sold
    abc 21-Sep-99 -6 Sold
    abc 21-Sep-99 19 Bought
    abc 29-Oct-99 -40 Sold

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I mocked up a table to simulate your set up.


    Table Values
    id portfolio asofdate pmv
    1 abc 01-Jul-99 0.2
    2 abc 03-Jul-99 0.31
    3 abc 09-Aug-99 -0.09
    4 abc 21-Sep-99 0.19
    5 abc 29-Oct-99 -0.4
    6 abc 21-Sep-99 -0.06


    Query sql
    Code:
    SELECT PortfolioTrans.portfolio, PortfolioTrans.asofdate,Round( (PortfolioTrans.pmv *100),2) as trans,
    iif(trans >0, "Bought","Sold") as TransType
    FROM PortfolioTrans
    WHERE (((PortfolioTrans.asofdate) Between #1/1/1999# And #31-dec-99#))
    ORDER By asofdate,pmv
    Result

    portfolio asofdate trans TransType
    abc 01-Jul-99 20 Bought
    abc 03-Jul-99 31 Bought
    abc 09-Aug-99 -9 Sold
    abc 21-Sep-99 -6 Sold
    abc 21-Sep-99 19 Bought
    abc 29-Oct-99 -40 Sold



    id portfolio asofdate pmv
    1 abc 01-Jul-99 0.2
    2 abc 03-Jul-99 0.31
    3 abc 09-Aug-99 -0.09
    4 abc 21-Sep-99 0.19
    5 abc 29-Oct-99 -0.4
    6 abc 21-Sep-99 -0.06

    Post posting:
    I added few more records for 21-sep-99 to show the sort order more clearly --ascending on PMV within Date

    portfolio asofdate trans TransType
    abc 01-Jul-99 20 Bought
    abc 03-Jul-99 31 Bought
    abc 09-Aug-99 -9 Sold
    abc 21-Sep-99 -6 Sold
    abc 21-Sep-99 -4 Sold
    abc 21-Sep-99 -2.5 Sold
    abc 21-Sep-99 19 Bought
    abc 29-Oct-99 -40 Sold

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In my query I have: ORDER BY AsOfDate, PMV.

    The result that I get is sorted correctly by the transaction date, but not by the amount bought/sold; the order of the amount bought/sold in each transaction seems random.
    are you sure it is not just because it is displaying the negative values first?

  7. #7
    S2000magician is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    31
    Quote Originally Posted by Ajax View Post
    are you sure it is not just because it is displaying the negative values first?
    It wasn't that, and, to be honest, I don't know what the problem was.

    What I do know is that I followed the suggestions here and it's working fine now.

    I think that both of my brain cells are worn out.

    Thanks for the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. UDF in an IN statement not working properly in query
    By CodeLiftSleep in forum Access
    Replies: 6
    Last Post: 02-01-2018, 11:09 PM
  2. Replies: 10
    Last Post: 05-09-2014, 10:54 AM
  3. Query not properly executing within VBA code
    By kewelch in forum Queries
    Replies: 1
    Last Post: 07-17-2013, 01:12 PM
  4. Query not functioninng Properly
    By jo15765 in forum Queries
    Replies: 16
    Last Post: 06-26-2011, 12:24 AM
  5. Query on chart not working properly!
    By Sim_ in forum Queries
    Replies: 0
    Last Post: 10-28-2009, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums