Vba?

[cpc]

Hi,

Looks pretty basic but I seem to be drawing a blank here. I havae a simple spreadsheet:
Ref# Rev
97 b
98 c
99 c
99 e
100 c
100 b
101 a
102 b

I need to create a simple report but remove the duplicates (ex. Ref# 99,100). I need to delete the older Rev's (Ex Ref# 99 Rev C, Ref# 100 Rev B).

Is this done throughRecordsets? will an SQL query do the trick?

THANK YOU.

 

[pr2-eugin]

Try this Query..

DELETE * FROM theTableName 
WHERE [Ref#] In (SELECT [Ref#] FROM theTableName GROUP BY [Ref#] HAVING Count([Ref#]) > 1);

BE CAREFUL of DELETE Queries ! You have been warned !

 

[cpc]

Thanks for the quick turn around! This query deletes all records with duplicate Ref#s. I would like to keep the Ref# that has the most recent revision.
Example: Keep 99e delete 99c

 

[namliam]

Why delete records you may need in the future? why not simply extract only the most recent ones?

SELECT [Ref#], Max([Ref]) LastRef FROM theTableName GROUP BY [Ref#]

Just FYI, I can advice you to not use something like # in column names, it can cause issues here or there.... Instead use a proper NR or Number

 

[cpc]

Thanks for the reminder on the # in column names. Changed it to Ref. Also great idea not to delete records. Unfortunately I am unfamiliar with the Max([Ref]) Last Ref function. Would you have an example of this?

Again, Thanks.

 

[MSAccessRookie]

Thanks for the reminder on the # in column names. Changed it to Ref. Also great idea not to delete records. Unfortunately I am unfamiliar with the Max([Ref]) Last Ref function. Would you have an example of this?

Again, Thanks.

Actually, I believe that he did, but I think he had a Type-o. Below is an updated version of the Query that had your latest mod in it.

I believe that the Ref was intended to be Rev, and that LastRev is an Alias label for the returned value of the Max() Function.

-- Rookie

SELECT [Ref], Max([B][COLOR=red][Rev][/COLOR][/B]) [B][COLOR=red]AS [/COLOR][COLOR=red]LastRev[/COLOR][/B] FROM theTableName GROUP BY [Ref]

 

[namliam]

holy typo batrookie, well spotted and will fixed

 

[cpc]

Actually, I believe that he did, but I think he had a Type-o. Below is an updated version of the Query that had your latest mod in it.

I believe that the Ref was intended to be Rev, and that LastRev is an Alias label for the returned value of the Max() Function.

-- Rookie

SELECT [Ref], Max([B][COLOR=red][Rev][/COLOR][/B]) [B][COLOR=red]AS [/COLOR][COLOR=red]LastRev[/COLOR][/B] FROM theTableName GROUP BY [Ref]

Brilliant in its simplicity!! Always learn something new with you guys!
THANK YOU!