Results 1 to 8 of 8
  1. #1
    crusader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4

    Transaction Details Query for all but first 4 Transaction Dates (Total Transactions Vary)

    I am using Access 2010 and have tried to search for an answer to the following question without success. I came across some SQL code, but had difficulty matching it to my specific needs. I don't know VBA/macros.



    I need to build a query that will show all the fields for each customer, limited to all but the first 4 transaction dates - grouped by customer. In other words, if the customer has 9 transactions, I need details of the last 5; if the customer has 7 transactions, I need details of the last 3 transactions - by date.

    Conversely, I also need to build a query that will give details of the first/last n (lets say 4) transactions - again, by date.

    All help will be greatly appreciated!

    CustomerID OrderID OrderDate Amount
    1 1 1/1/15 200
    1 2 1/6/15 300
    2 3 1/2/15 50
    1 4 1/3/15 60
    3 5 1/2/15 900
    2 6 1/9/15 400
    1 7 1/12/15 300
    3 8 1/3/15 100
    2 9 2/1/15 200
    1 10 2/2/15 66
    3 11 2/15/15 90
    2 12 2/11/15 500
    2 13 2/9/15 100
    1 14 2/9/15 300
    3 15 2/28/15 250
    1 16 2/27/15 444
    2 17 2/16/15 700
    3 18 2/20/15 300
    3 19 1/19/15 650
    2 20 1/30/15 125
    3 21 1/1/15 700
    1 22 2/3/15 100
    1 23 2/5/15 250
    3 24 2/7/15 190
    2 25 2/22/15 900

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You will need to build a subquery. Look at this Link http://allenbrowne.com/subquery-01.html

    Here is an example of selecting the top 4 dates in your table

    SELECT Sheet1a.CustomerID, Sheet1a.OrderID, Sheet1a.OrderDate, Sheet1a.Amount
    FROM Sheet1a
    WHERE (((Sheet1a.OrderDate) In (Select top 4 [Sheet1a].OrderDate from sheet1a)))
    GROUP BY Sheet1a.CustomerID, Sheet1a.OrderID, Sheet1a.OrderDate, Sheet1a.Amount;

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    there is an embedded feature in query design that I rarely use but may give you what you seek; In the ribbon while in Query Design - look for the 'Return' feature; I believe that might work when coupled with the appropriate sort criteria.

  4. #4
    crusader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4

    Not getting the result I am looking for

    Quote Originally Posted by alansidman View Post
    Here is an example of selecting the top 4 dates in your table

    SELECT Sheet1a.CustomerID, Sheet1a.OrderID, Sheet1a.OrderDate, Sheet1a.Amount
    FROM Sheet1a
    WHERE (((Sheet1a.OrderDate) In (Select top 4 [Sheet1a].OrderDate from sheet1a)))
    GROUP BY Sheet1a.CustomerID, Sheet1a.OrderID, Sheet1a.OrderDate, Sheet1a.Amount;
    Thank you for the suggestion, alansidman.

    Running the SQL returns the following result.

    CustomerID OrderID OrderDate Amount
    1 1 01/01/2015 $200.00
    1 2 01/06/2015 $300.00
    1 4 01/03/2015 $60.00
    2 3 01/02/2015 $50.00
    3 5 01/02/2015 $900.00
    3 8 01/03/2015 $100.00
    3 21 01/01/2015 $700.00

    What I am looking for is top 4 records for each customer - for a total of 12 records given that I have 3 distinct customers.

  5. #5
    crusader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Quote Originally Posted by NTC View Post
    there is an embedded feature in query design that I rarely use but may give you what you seek; In the ribbon while in Query Design - look for the 'Return' feature; I believe that might work when coupled with the appropriate sort criteria.
    Thank you for the suggestion, NTC. From what I can tell, the Return feature is largely a display option as opposed to affecting the underlying query.

    Alansidman's suggestion seems the route to go; what it needs is code that will pull the first 4 transactions (by date) for each customer. As the code stands, it pulls the first 4 dates without factoring in individual customers.

    I hope I am explaining my need clearly. I will be happy to provide further clarification if needed. All help will be greatly appreciated.

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I understand your issue and have been attempting to resolve using multiple queries without success. Will continue to work, but hopefully someone else will have a breakthrough as I have minimal available time today.

  7. #7
    crusader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4

    Thumbs up

    Issue resolved! The following code worked for me:

    Code:
    SELECT Table1.CustomerIDTable1.OrderIDTable1.OrderDateTable1.Amount
    FROM Table1
    WHERE Table1
    .OrderDate IN
    (Select top 4 OrderDate
    FROM Table1 
    AS Dupe
    where Dupe
    .CustomerID Table1.CustomerID
    Order by Dupe
    .CustomerID DESCDupe.OrderDate DESC)
    order BY Table1.CustomerIDTable1.OrderIDTable1.OrderDateTable1.Amount
    Alansidman and NTC: your support is greatly appreciated!

  8. #8
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Ok. Rembered this: http://www.datapigtechnologies.com/f...spergroup.html

    You will need to have a second table for dates.

    Edit: Glad you figured it out Same principle as above.

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

Similar Threads

  1. MS Query Last Transaction Date Formula Required
    By Maroota in forum SQL Server
    Replies: 1
    Last Post: 02-27-2015, 08:20 AM
  2. Replies: 17
    Last Post: 11-06-2013, 05:38 PM
  3. Transaction Log
    By gtimmies in forum Database Design
    Replies: 1
    Last Post: 05-27-2013, 03:27 PM
  4. Replies: 7
    Last Post: 11-14-2011, 05:59 PM
  5. SQL Transaction question
    By Mazdaspeed6 in forum Programming
    Replies: 4
    Last Post: 12-16-2010, 12:51 PM

Tags for this Thread

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