We help IT Professionals succeed at work.

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

7,243 Views
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
SELECT DISTINCT t1.CashDate, t1.AvailableCash
FROM YourTable t1
WHERE t1.DateID =
    (SELECT Max(t2.DatID) FROM YourTable t2 WHERE t2.CashDate = t1.CashDate)
Does your query have an Order By clause
See this link:
       http://office.microsoft.com/en-us/access/HP010322321033.aspx

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

Commented:
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;
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
What would be t2[ID] and Movimiento_Caja_Dia t2?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.