Hello,

I have a table that contains the fields Id, date, balance, name, address,
city, state, zip and phone. There are multiple records per individual.
What I am looking to do is create a query that will group the records by Id
and pick the record that has the latest date within each group and then zero
out the balance in all the other records.

For example

John Smith has 3 records dated 3/3/04 4/4/04 and 5/5/04 all with different
balances. Since 5/5/04 is the latest date I want to keep this record
unchanged and zero out the balance in the 3/3/04 and 4/4/04 records.

Is this possible easily?

Any help would be greatly appreciated!!

kevinjbarnes (AT) earthlink (DOT) net wrote:
select all fields but the date, group on all fileds but the date then
select max(date)--this would give you the last date

somehow you would then select all less than max(date) and somehow set
the balance to 0

or even something like this insert into--balance = 0 where < ( i thing
the less than sigh is right but do check it) less than (max(date)

yes,
it can be done but i do not know the syntax all i gave ws some idea as
to what the problem boils down to
hth
petr