>Thanks.
>
>I need this because my client need to sort out the records for them to schedule the production. The 4 fields are Row Material. If the row Materail are same or similar, they will keep then together to smooth the manufacturing shop floor process.

I see.

OK, I will try to outline my idea.

First, copy your records to a temporary table. From here, copy one record at a time to the destination table. As you copy each record, delete it from the temporary table.

Start by transfering the first record (transfer means: copy it to the final table, and delete it from the temporary table).

Now, find the closest record, with a UDF which I will outline immediately. The UDF will have to give a number, how many parts are matching, with the latest record you copied. The record with the highest number of matching parts will be copied.

Now, to the UDF. The comparison is especially simple if your tables are normalized. For instance, suppose that your component table has a field "parent" and a field "child" (parent contains child). Just copy all the children for Parent1 and for Parent2 to two cursors, and do something like:

select child from Temp1 where child in (select child from Temp2);
  into cursor Temp3;
  nofilter
NumberOfMatches = _tally
* or:
NumberOfMatches = reccount()

In fact, this solution is so simple (once you understand subqueries), that if your data is in the form you explained originally (comma-delimited), it would almost be worthwhile to copy it to a cursor, to use the powerful sub-query feature.

A similar alternative, without subqueries, is also possible:

select Temp1.Child, Temp2.Child;
  from Temp1 join Temp2 on Temp1.Child = Temp2.Child

Again, since you are only interested in the number of matches, you can use _tally or reccount() on the result.

HTH,

Hilmar.