Results 1 to 5 of 5
  1. #1
    coralis is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    2

    Second to Most Recent Date

    Hi,

    I am looking for some help tweaking a query. I have already got one showing me every record from a table with the most recent date as the criteria for selecting that query.

    This is the current query I have which gives me the most recent record based on date for every item in the database. I want to know what the second to most recent is, so I can compare the two in a report with conditional formatting highlighting an increase in price.

    Code:
    SELECT Price_Change_Log_out.CSN_Item_Code, Price_Change_Log_out.Date_of_Purchase, Price_Change_Log_out.Unit_Price, Price_Change_Log_out.Preferred_Price_for_Costing
    FROM Price_Change_Log AS Price_Change_Log_out
    WHERE (((Exists (SELECT 1
                       FROM Price_Change_Log Price_Change_Log_in
                      WHERE Price_Change_Log_in.CSN_Item_Code = Price_Change_Log_out.CSN_Item_Code
                   GROUP BY Price_Change_Log_in.CSN_Item_Code
                     HAVING MAX(Price_Change_Log_in.Date_of_Purchase) = Price_Change_Log_out.Date_of_Purchase))<>False));
    I am sure the issue lies with either "Select 1" requiring to be "Select 2" or "Top 2" or something to that effect, or a change in the "HAVING MAX" section, but I am not very experienced and there are not a lot of help articles out there on this one.



    Any help would be much appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    usual way would be a criteria along these lines

    WHERE ID IN (SELECT TOP 2 ID FROM sometable T WHERE Code=sometable.code ORDER BY myDate Desc, ID)

    not clear to me why you are using a cartesian subquery so please adapt as required. You also don't appear to have an ID, but perhaps that's because you don't need it for this query

    Note that the TOP predicate can return more that the specified number of records if two or more records have the same myDate which is why you use ID, not date - otherwise you will potentially end up with duplicates. You will probably need to experiment a bit

  3. #3
    coralis is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    2
    Quote Originally Posted by Ajax View Post
    usual way would be a criteria along these lines

    WHERE ID IN (SELECT TOP 2 ID FROM sometable T WHERE Code=sometable.code ORDER BY myDate Desc, ID)

    not clear to me why you are using a cartesian subquery so please adapt as required. You also don't appear to have an ID, but perhaps that's because you don't need it for this query

    Note that the TOP predicate can return more that the specified number of records if two or more records have the same myDate which is why you use ID, not date - otherwise you will potentially end up with duplicates. You will probably need to experiment a bit
    Hi Ajax,

    Thank you for your reply.

    Unfortunately I am quite new to all of this, and that code was auto generated based on some methods I used about 9 months ago and cannot exactly remember how I came by it, but almost every thread on the internet regarding MS Access comes down to "what is the code" so I thought it would help.

    Currently it displays a record for each Item, based on the most recent date. All I want is another column showing the same items price based on the previous entry under that item code.

    I will play around with the criteria you gave me and see how I go, thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review http://allenbrowne.com/subquery-01.html#AnotherRecord

    Provide sample data if you need more help.
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    This example is based on your original code and should produce the same result- I have reduced the alias names to make the code easier to read . This should be significantly quicker than your current query if you have a large number of records

    Code:
    SELECT LO.CSN_Item_Code, LO.Date_of_Purchase, LO.Unit_Price, LO.Preferred_Price_for_Costing
    FROM Price_Change_Log LO INNER JOIN (SELECT CSN_Item_Code, Max(Date_of_Purchase) as MaxDate FROM Price_Change_Log GROUP BY CSN_Item_code) MD ON LO.CSN_Item_code=MD.CSN_Item_Code AND LO.Date_Of_Purchase=MD.MaxDate
    check that is the case

    if it is then try this code which should give you the previous unit price as well

    Code:
    SELECT LO.CSN_Item_Code, LO.Date_of_Purchase, LO.Unit_Price, LO.Preferred_Price_for_Costing, LI.Unit_Price
    FROM (Price_Change_Log LO INNER JOIN (SELECT CSN_Item_Code, Max(Date_of_Purchase) as MaxDate FROM Price_Change_Log GROUP BY CSN_Item_code) MD ON LO.CSN_Item_code=MD.CSN_Item_Code AND LO.Date_Of_Purchase=MD.MaxDate) LEFT JOIN Price_Change_Log LI ON LO.Item_Code=LI.Item_Code
    WHERE LI.Date_of_Purchase= (SELECT Max(Date_of_Purchase) FROM Price_Change_Log WHERE CSN_Item_code=LO.CSN_Item_code AND Date_of_Purchase<LO.Date_of_Purchase) OR LI.Date_of_Purchase is Null
    subqueries by their nature are slow (but not as slow as domain queries) so ensure all fields used in joins and criteria are indexed (CSN_Item_Code and Date_of_Purchase in in this case)

    code has been freetyped - so watch out for typo's I may have created inadvertently

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

Similar Threads

  1. Most recent date
    By OCStan in forum Forms
    Replies: 4
    Last Post: 10-20-2016, 10:45 AM
  2. Query for most recent date
    By benjamin.m.winchester in forum Queries
    Replies: 3
    Last Post: 12-22-2015, 01:33 PM
  3. Replies: 1
    Last Post: 10-02-2015, 12:28 PM
  4. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  5. Most recent Date query
    By SmartestIdiot in forum Queries
    Replies: 1
    Last Post: 01-11-2014, 07:56 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