Delete all records matching a field except for one based on an includeddate field

[naniboujou]

I have a single table with records that contain a date field and a
separate key field. Following an update that appends similar records, I
want to then eliminate all those older records that match the key field
and also have an earlier value in the date field. I do want to keep the
latest however. In other words, here comes a new, dated record on this
topic, so dump any and all older ones that match the key field.

This has to be a commonly used process, but I am new enough at this I
assume I am missing the obvious.

 

[Jeff Boyce]

Usually, a "key" field is considered unique, so I'm having trouble
understanding how you could have more than one "match"?

Rather than delete and re-load, would it be appropriate to simply update the
existing record?

Good luck

Jeff Boyce
<Access MVP>

 

[naniboujou]

Jeff,

Sorry for the poor choice of words. Indeed the field in question has not
been marked as a primariy key because then I couldn't do the append of
new data -- as you said, primary keys must be unique. So I append
several updated records (I'll even tell you what these records contain:
new information on companies I am following for investing and the field
in question is the Ticker symbol). OK, new information on already
existing stocks in the data base is appended, each with a field that
contains the ticker plus another field that is the date of this update
(and few other fields, of course). Now I want to purge all records in
that table that have the same ticker and also have a date field that is
less than the latest. I would be left with every record having a unique
ticker, but over time many different dates for the fields because the
new info trickles in over time a few stocks at a time.

What I think I want to do (happy to hear a better way!) is to first
merge in the new data and then expunge the old. But that criteria is
being made by looking at other records in the same table. I have tried a
few delete queries and the preview even shows the records I hope to
dump. When when I run the query, Access invariably says the records in
question cannot be deleted (with no further explanation).

Steve

 

[naniboujou]

Jeff,

Since my last reply I tried what you said--to work the problem on the
update side of things. Success. This will work out, but I was hoping the
way I was approaching it would have allowed the delete process to be
delayed at times to the point where there were three or more records
with the same ticker and all different dates. I was hoping my "append,
append, etc., followed by a delete" approach would get rid of multiple
records of stale entries, leaving only the latest. Maybe I was reaching
to far???

Thanks for help. I am working alone (have a lot of computer experience
but am admittedly very, very green at data base stuff) and would be lost
without this community!

Steve