Automatically remove duplicate data

[vivi]

Hi there, I am a new access database user and hoping someone can help me with
this problem.

I have a lookup table which I have a number of records, however, in order
for it to be linked to a data table I have to re-create the lookup table with
the same format. For example, in lookup table, the record will have a project
number PRO_1234, but for the data table, it will only show 1234. I have done
this part using formula to extract part of the project number (as it always
have a _ in front of it).

The problem starts when there are subsequent work for the project and the
project system automatically assign the project as PRO_1234_A. I have
successfully eliminate "_A" as well, nevertheless, it will now show two
extries for 1234 in my new lookup table. How can I automatically delete
records when there are duplicate numbers??I guess I will have to set up a new
query for this and probably create a macro, but not sure what else I can
do...please help!!

Thanks a lot for helping me, I hope I have explained myself clearly, if not
please let me know.

Thanks again

 

[Arvin Meyer [MVP]]

Use a find duplicates query (which is one of the query wizards) to identify
them. Then select each one you want to delete and hit the delete key. If
there are very many of them, you'll need to identify a way to select 1 of
them for deletion and use that as a criteria in a delete query.