Remove Duplicates with new extensions

[mechInferno]

Hey, I have a interesting problem. I am creating a report which has a few hundred rows of data. Within the report I want display a proposalstring, status of the proposal, and custName. The proposalstring's have many duplications but with a Revision. Looks like this:

P596-392 Rev.1
p596-392 Rev.2
p596-392
P486-954 Rev.1
and so on....
I want to know what I can do to only get the recent versions of the proposalstrings like ...Rev.2 and Rev.3.
Is there anything I can do?

 

[hneal98]

use mid function to extract the revision number and then use the max function to get the max number. Also group by the Proposalstring.

 

[mechInferno]

I have tried with the mid function but, I want to remove the row of data from the query when new revisions are put in the report. So, when I have all the proposalstrings listed, I want only the rows of data that have the recent revisions and the ones which don't hav any revisions. The old revisions I don't want to display.

 

[hneal98]

Can you post what you have tried. I still think you will need a combination of the mid and max functions.

 

[mechInferno]

I just tried:
Max(Mid([proposalstring,10,16))
which took off the Rev parts but, I need to remove the whole row of data.
I have also tried some if statements to compare the strings after the rev part is gone but it still doesn't take out the entire row.

Is there a delete command that can work only on the query and ont effect the table?

 

[hneal98]

I need to see your whole query. Can you post the SQL version of your query?