Results 1 to 6 of 6
  1. #1
    jlfinger is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Location
    Ridgefield, CT
    Posts
    3

    Make Report Conform to Query Order

    There are 11 recognized Sectors in the U.S. stock market. Those Sectors are characterized by 11 Select Sector SPDR ETFs, each of which has a symbol beginning with the letter "X".

    The Goal is create a report, grouped by Sector, that places the Select Sector SPDR ETF at the top of its Group, followed by the stocks in that Sector in alphabetical order (by symbol).

    I have created a query that accomplishes the order I want. However, the reports I create insist on overall alphabetical order (by symbol), which places the ETF at, or near, the bottom of the grouping.
    How do I get the report to follow query order? (there needs to be a smiley for banging one's head against a wall).
    No doubt it's a simple, "duh!" answer.
    TIA,
    Jim

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Exactly how did you accomplish this in query? Provide query SQL.

    Use report Sorting & Grouping features. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jlfinger is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Location
    Ridgefield, CT
    Posts
    3
    Query as follows:
    SELECT tblimport.Date, tblimport.Account, [Base List].Symbol, [Base List].Description, [Base List].SecSym, [Base List].Sector, [Base List].SPDR, tblimport.Price, tblimport.Quantity, tblimport.Price*tblimport.Quantity AS MktVal, TblClients.[Last Name], TblSecWeight.SecWeight, TblSecWeight.SecWghtDate
    FROM (TblClients INNER JOIN (tblimport INNER JOIN [Base List] ON tblimport.Symbol = [Base List].Symbol) ON TblClients.Account = tblimport.Account) INNER JOIN TblSecWeight ON [Base List].SecSym = TblSecWeight.SecSym
    WHERE (((tblimport.Date)=#10/27/2021#) AND ((tblimport.Account)="ALL") AND (([Base List].SPDR)<2))
    ORDER BY [Base List].SecSym, [Base List].Sector, [Base List].SPDR DESC , [Base List].Symbol;

    Comment:
    [Base List].SecSym has (for our purposes) one of the 11 Sector SPDR symbols that correspond to the Sector of the Security.
    [Base List].Sector has the full name of the Sector
    [Base List].SPDR has values: 0 (for all securities indentifiable within a Sector), 1 (for Sector SPDR ETF), 2 (all other: mutual funds, etc)
    [Base List].Symbol has the stock symbol

    Query Results: Note that Materials and Energy Sectors contain Select Sector SPDR ETFs... and that they show at the top of that Sector. Same is true with other Sectors that have Select Sector SPDR ETFs.
    But, the Report invariably pushes the ETF into alphabetical order by Symbol... rather than its place in the Query order.
    Click image for larger version. 

Name:	Query Results.jpg 
Views:	10 
Size:	180.6 KB 
ID:	46778

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you try report Sorting & Grouping?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Reports take NO NOTICE of source sorting. It must be done in report.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by jlfinger View Post
    <Snip> (there needs to be a smiley for banging one's head against a wall). </Snip>
    Like this?? (I feel like this a lot)

    Go to the smiley menu, then click "More"...

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

Similar Threads

  1. Report order different than source query
    By Bazsl in forum Reports
    Replies: 2
    Last Post: 11-29-2021, 03:14 PM
  2. Replies: 13
    Last Post: 11-04-2021, 07:50 PM
  3. Replies: 1
    Last Post: 07-22-2014, 12:53 PM
  4. Replies: 5
    Last Post: 11-07-2012, 08:15 AM
  5. Make TEXTBOXES sort by DESCENDING order
    By taimysho0 in forum Programming
    Replies: 1
    Last Post: 12-05-2011, 04:52 PM

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