delete duplicate records via VB

[hceonetman]

Just starting out adding to VB code to Access 2000 apps, and I'm not sure how to do this. I used a query to populate a table with employees and their sick days, sorted by empl name, then by sick day descending. I'm only interested in the most recent date - the first record for each employee, so I'd like to step thru the table deleting all but the first record for each empl. I'd like a module that could be called from the same macro that creates the table, leaving me with each employee and his most recent sick date.
I know it sounds simple, but like I said, I'm a newcomer to VB programming. Any suggestions on best websites or books on Access VB coding also appreciated.

Thanks for your help,
HCEONETMAN

 

[gol4]

this uses a sub query to find the maximum date for the employee and if the date don't match it deletes the record

docmd.runsql &quot;delete * from table2 where table2.datefld <>
(SELECT Max(q.datefld) AS maxdt FROM table2 as q where q.empid = table2.empid);&quot;

pretty sure it is close replace table2 with your table name and datefld with your datefield name and empid with what ever you call yours.

the q is an alias so don't change that

as in the case of all action queries backup before you run it and test for the return
to test
select * from table2 where table2.datefld <>
(SELECT Max(q.datefld) AS maxdt FROM table2 as q where q.empid= table2.empid)

hope that works for you

 

[hceonetman]

Gol4,
Looks like the right idea, but it gives me a compile error, with the usual (Microsoft) lack of explanation. I've also tried creating a query in the regular way to do the same thing, with the same results. I don't seem to have the hang of MAX or DISTINCTROW as of yet. I'll keep hacking at it.

Thanks,
HCEONETMAN