Update Query Last Record in Table only

[Guest]

I have an update query that adds 2 values together and stores them in a 3rd
field in the same table i wish this query just to run for the last record in
the table & not the entire table, what would be the query critera for this?

 

[Guest]

It is never a good idea to store computed values in a table when you already
have the values that are merged through some function f(x,y). Too many
chances for one of the other fields to change and not update the third field.

If you insist on doing this, and you have an autonumber field in your table
you could do something like:

Update yourTable
SET [field3] = [field1] + [field2]
WHERE [ID] = DMAX("ID", "yourTable")

HTH
Dale

 

[Gary Walter]

I have an update query that adds 2 values together and stores them in a 3rd
field in the same table i wish this query just to run for the last record
in
the table & not the entire table, what would be the query critera for
this?

Hi Steve,

Some field will have to define the "last record."

For example, if you have an autonumber field "ID"

you may get by with filtering for the highest ID

UPDATE yurtable As T
SET T.f3 = T.f1 + T.f2
WHERE T.ID =
(SELECT Max(q.ID)
FROM yurtable AS q);

or maybe a date field determines the "last record"

UPDATE yurtable As T
SET T.f3 = T.f1 + T.f2
WHERE T.DateField =
(SELECT Max(q.DateField)
FROM yurtable AS q);

Without some type of field as above,
the query has no way to know the "last record."

If you just added the record (say in code)
you "can" get the IDENTITY of the last record...
and use that value in a query update where you
construct the SQL in code.

There may be other scenarios, but that is all
that comes to mind now...

good luck,

gary

 

[Guest]

Thanks for the reply Dale, I do have a autonumber field in the table. If I
folllow your suggestion i believe my code would be as follows
Update OrderTable
SET [Cost] = [Cost a] + [Cost b]
WHERE [Our Ref] = DMAX("Our Ref", "OrderTable")

is it possible for me to use the last line of code "WHERE [Our Ref] =
DMAX("Our Ref", "OrderTable")" as the criteria field in the query as opposed
to using VBA code as an event procedure

It is never a good idea to store computed values in a table when you already
have the values that are merged through some function f(x,y). Too many
chances for one of the other fields to change and not update the third field.

If you insist on doing this, and you have an autonumber field in your table
you could do something like:

Update yourTable
SET [field3] = [field1] + [field2]
WHERE [ID] = DMAX("ID", "yourTable")

HTH
Dale

I have an update query that adds 2 values together and stores them in a 3rd
field in the same table i wish this query just to run for the last record in
the table & not the entire table, what would be the query critera for this?

 

[John W. Vinson]

Thanks for the reply Dale, I do have a autonumber field in the table. If I
folllow your suggestion i believe my code would be as follows
Update OrderTable
SET [Cost] = [Cost a] + [Cost b]
WHERE [Our Ref] = DMAX("Our Ref", "OrderTable")

is it possible for me to use the last line of code "WHERE [Our Ref] =
DMAX("Our Ref", "OrderTable")" as the criteria field in the query as opposed
to using VBA code as an event procedure

What you posted is NOT VBA code nor is it an event procedure; it's a query
using the DMax() call as a criterion.

Just copy and paste it into the SQL view of a new query and you'll be able to
switch back to the query grid view.


John W. Vinson [MVP]

 

[John W. Vinson]

I have an update query that adds 2 values together and stores them in a 3rd
field in the same table

Not a good idea.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]