Display the last "n" records before the last in a MS Access Query

Posted on 2012-09-19
Last Modified: 2012-09-19

I have a table/query that lists the daily prices for particular commodities.  The query is called qry_Prices and contains the following fields:


What I want to do is ultimately create a report/graph that shows the price movement over the past 10 days from the last inputted date for each commodity (don't worry too much about whether each commodity will have a differenct "last" date.  This is not the case since prices are inputted for each commodity daily so the last date will always be the same).  The last inputted date always is the last record.

The results in the query will obviously "roll" since I will be entering a price everyday so I need something like:

[Last Entered Date] - 10

So, in summary I would like to see:


where the date range is between the [LastDate/Record] and [LastDate/Record] - 10.

Hope that you can help!
Question by:staceymoore
    LVL 30

    Accepted Solution

    Try this for table a(f1, f2, dttm1)

    SELECT a.f1, a.f2, a.dttm1
    FROM a
    where a.dttm1 between (select max(a.dttm1) from a)-10  and (select max(a.dttm1) from a)  ;

    Open in new window


    Author Closing Comment

    thanks again for your assistance!
    LVL 30

    Expert Comment


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now