Results 1 to 6 of 6
  1. #1
    kdilag is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    3

    Select Query after Sort

    Hello,

    I have a transaction data set with these fields see attached txt file


    How would I write the query that returns the first TRAN_TYPE B record (based on TRAN_ID) for each CLIENT_ID thus rendering a 3 row selection. IOW first sorting by CLIENT_ID asc, TRAN_TYPE desc, TRAN_ID asc then selecting the first row for each client.

    Can anyone describe how to set this query up in access?



    Thanks soooo much if you can help me,

    K

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Just to be clear, you want your output to look like this?

    CLIENT_ID|TRAN_ID|TRAN_TYPE
    1|606|B
    2|646|B
    3|441|B

  3. #3
    kdilag is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    3
    Yes - that is what I would like the query to return.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Create a query that just selects the B records

    query Name qryTheBs

    SELECT tblClientTrans.Client_ID, tblClientTrans.TRAN_ID, tblClientTrans.TRAN_TYPE
    FROM tblClientTrans
    WHERE (((tblClientTrans.TRAN_TYPE)="B"))
    ORDER BY tblClientTrans.Client_ID, tblClientTrans.TRAN_ID;


    Then you will need a nested query as follows:

    SELECT qryTheBs.Client_ID, qryTheBs.TRAN_ID, qryTheBs.TRAN_TYPE
    FROM qryTheBs
    WHERE qryTheBs.Tran_ID in (SELECT TOP 1 Tran_ID FROM qryTheBs as Q1 WHERE Q1.Client_ID=qryTheBs.Client_ID ORDER BY Q1.TRAN_ID ASC)
    ORDER BY qryTheBs.Client_ID, qryTheBs.TRAN_ID;

  5. #5
    kdilag is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    3
    Thanks jzwp11!

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome!

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

Similar Threads

  1. sort query by two fields, populate one
    By Dee300 in forum Queries
    Replies: 3
    Last Post: 09-15-2011, 03:22 PM
  2. Sort Query
    By jice89 in forum Queries
    Replies: 3
    Last Post: 05-06-2011, 05:56 PM
  3. Replies: 3
    Last Post: 04-19-2011, 06:41 PM
  4. Replies: 2
    Last Post: 01-31-2011, 08:31 AM
  5. How to sort by three sort orders
    By captgnvr in forum Access
    Replies: 4
    Last Post: 11-09-2009, 07:30 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