Hi Bret,

>I would expect a multiple-insurance plan situation to be more complicated, but if the immediate goal is to identify the first record in each group in order not to zero out its claim amount, I can think of two ways to do it with SQL. You can try them both and see which is faster, and whether either is faster than SCAN (which I'm not promising). You may also have to adjust them a bit in case I got a detail wrong:

My example was simplified, sorry if it doesn't make sense. (The example seemed good at the time <G>) The report needs to sum the dollar amounts and give a "Cross-Reference" to current eligibility. Not to show which Plan will be charged.

>
>Way #1 - Select all the records in the eligibility table in the order you want:
>
>SELECT * FROM elig INTO CURSOR eligord ORDER BY mem_num
>
>If you really just want the first plan_id for each member to pay, I suppose your record number order reflects that. Now use RECNO() to number them all:
>
>SELECT *, RECNO AS recnums FROM eligord INTO CURSOR elignumbers ORDER BY mem_num, recnums DESC
>
>Notice the DESC. That will end up selecting the first plan from each group in this next query, which groups on mem_num and has no aggregate function for plan_id:
>
>SELECT mem_num, plan_id FROM elignumbers INTO CURSOR firstplan GROUP BY mem_num
>
>This cursor contains the first plan for each member. You can now LEFT OUTER JOIN your other stuff to this cursor and include, say, firstplan.plan_id which will be null in the resulting cursor for plans that aren't the first one. Then make clm_amt = IIF(ISNULL(firstplan.plan_id,0,claims.clm_amt)
>
>Way #2:
>
>SELECT mem_num, plan_id, SUM(1) AS plannum FROM elig INNER JOIN elig AS elig1 ON elig.mem_num = elig1.mem_num AND elig.plan_id >= elig1.plan_id INTO CURSOR planord GROUP BY 1,2
>
>You might have to reverse the direction of the non-equi-join - I often get those backwards. These non-equi-join queries are slow, but with just this one you have already identified the first plan for each member. You can INNER JOIN this cursor with your other stuff and make clm_amt = IIF(firstplan.plannum>0,0,claims.clm_amt)
>
>On both these queries I have some details to be worked out because my own test examples don't exactly match yours and because it's lunchtime. :)

Thanks for the ideas!
I always forget about non-equal joins. I'll have to give these a try. I won't be able until next week, however as I'm getting ready to go to DevConnections. Are you going to be there?