Ms Access Query using LAST as criteria to find a record not working and bringing RANDOM Restuls

Posted on 2007-10-13
Last Modified: 2008-05-21
I run an application using Ms Access.  In One of the Queries I used I need to find the last record of the list, in a daily basis I post the last Value of Cash available along with the date, therefore I have a table with the DateID which is an autonumber, date, and CashValue.  The problem I am currently having is that I use this query which looks for the LAST record of the Date and the Last Record of CashValue, but now If I run this query randomly is taking a record which is not the last, for Instance right now my last record would be for October 2, and the query shows as result May 23rd, and the CashValue correspondent to May 23rd.
I have resolve this issue in the past by copying the data from the table to  Excel, then delete the data in the Access Table, then Compact and Repair, and Paste the data back in the Access Table, but It is not working anymore.  I checked relationships and they are fine, so I don't know what is going on and why I am having this result.  I would like to be able to find a solution to this problem.
Question by:camilo_gs
    LVL 92

    Expert Comment

    by:Patrick Matthews
    SELECT DISTINCT t1.CashDate, t1.AvailableCash
    FROM YourTable t1
    WHERE t1.DateID =
        (SELECT Max(t2.DatID) FROM YourTable t2 WHERE t2.CashDate = t1.CashDate)
    LVL 38

    Expert Comment

    Does your query have an Order By clause
    See this link:

    Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.

    Author Comment

    This si the current Query I am using:
    SELECT Last(Movimiento_Caja_Dia.Fecha) AS ÚltimoDeFecha, Last(Movimiento_Caja_Dia.Caja) AS ÚltimoDeCaja
    FROM Movimiento_Caja_Dia;
    LVL 92

    Accepted Solution


    Assuming you have an ID field on that table that is an auto number...

    SELECT t1.Fecha, t1.Caja
    FROM Movimiento_Caja_Dia t1
    WHERE t1.[ID] = (SELECT Max(t2.[ID]) FROM Movimiento_Caja_Dia t2)



    Author Comment

    What would be t2[ID] and Movimiento_Caja_Dia t2?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now