to find duplicate records from the table and mark them latest based on revision no.

[rushitshah]

Hello People.
I need some help coding with an application.

I have a table that includes forecast of different parameters for different products. So when ever new forecast data is added the already existing data for same product and parameter is not deleted, but there is a field called revision, so old data record is marked as 0 and new is marked as 1 to indicated the revision 1 indicates new forecast. It can go like 2, 3....

What I want is to comapre the latest records for different products and their parameters from the table.

So I can do a duplicate record query, that will give me all the duplicates for certain product and parameter. The other records remaining in the table now are only single, so they can be latest. And from the duplicate query I need to take highest revision no. for the latest record.

After that I need to merge all those, highest revision no. records from duplicate query and the single records (The records in the table other than the duplicate query records)

Need some logical and code help with this.

Thanks for your time.

 

[workmad3]

How about just a single query along the lines of the following SQL statement

SELECT *, DISTINCT productID FROM <table> ORDER BY Revision DESC

If the * doesnt work, then replace it with all the records you need to view in this query.

What this should do though is return the highest revision of all distinct product id's, essentially giving you a recordset containing all the highest of the duplicate records and all the single records as well.

Example of use (ADODB)

<Create ADODB connection>
Dim rs As ADODB.RecordSet
Dim sql As String

sql = "SELECT *, DISTINCT productID FROM producttable ORDER BY Revision DESC"
Set rs = conn.execute (sql)

<run through recordset here>

or using DAO

Dim rs As DAO.RecordSet
Dim sql As String

sql = "SELECT *, DISTINCT productID FROM producttable ORDER BY Revision DESC"
Set rs = CurrentDB.OpenRecordset (sql)

<run through recordset here>

Hope this helps

 

[rushitshah]

Hi..Thx...
But i do not have productID in my table.

In place of that can I put combination of 2-3 fields to make a unique combination?

and how to do that?

 

[workmad3]

SELECT *, DISTINCT <field1> & <field2> & <field3> AS ProductID FROM <table> ORDER BY Revision DESC

as so on, adding as many fields into the distinct row as you need