Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-10-13
5
Medium Priority
?
7,223 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.
0
Comment
Question by:camilo_gs
  • 2
  • 2
5 Comments
 
LVL 93

Expert Comment

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

Expert Comment

by:puppydogbuddy
ID: 20070979
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.
0
 

Author Comment

by:camilo_gs
ID: 20072032
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;
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 20072097
camilo_gs,

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)

Regards,

Patrick
0
 

Author Comment

by:camilo_gs
ID: 20072142
What would be t2[ID] and Movimiento_Caja_Dia t2?
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

571 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