Using last record in a query

D

Delboy

Hi
I have an update query that uses a date field in one table as the
update criteria for another table. I want to be able to set the
criteria to use the last record entered in first table. I've tried
putting 'last' Max etc but none work.
Havnt done this in code, just in the design view.
Thanks
Derek
 
K

KARL DEWEY

Create a separate totals query with the key field and date field. Group by
key field and Max the date.
Use the totals query joined in your update query.
 
M

Michel Walsh

Hi,


You cannot have a query marked as updateable as soon as you have an
aggregate (elsewhere than in the WHERE clause and as a subquery, there).


UPDATE a INNER JOIN Tableb As b ON ...
SET a.f1=b.g1
WHERE b.dateTime=(SELECT MAX(c.dateTime) FROM Tableb As c WHERE c.something
= b.something)



Here, the subquery pick the maximum dateTime (where 'something' occurs).
The update would then occurs only for b-records that also satisfy the
condition that b-record is the one with the same date as the latest date
returned by the sub-query.



Another alternative is to use DMAX(), DSUM(), etc.



Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


The outermost query won't be updateable, unless you pump the result of the
first query into a (temp) table and use that table (not the query with the
MAX aggregate).


Vanderghast, Access MVP
 
J

John Spencer

You will need to use the DMAX function to get the date.

Access will not let you use the SQL aggregate functions in an update query.

Since you did not post any details, it is hard to give you a detailed
response.

DMax("SomeDateField","SomeTableName")

whoops. You said as criteria. Can you post the sql that is not working?
 
Top