John,
Are you assuming that GROUP BY will always give you the last record entered? That may be the usual behavior, but it's really not a feature of GROUP BY. You can easily determine if the CDX is the problem by deleting and recreating it. My guess is that you're expecting something from GROUP BY that isn't really a feature.
>I have a payment servicing application that has a fairly simple table structure: payor.dbf for the parent table and payment.dbf for the child table. Both are free tables with structural CDX index files.
>
>About one time out of 200, a record is appended to the payment table (no relations set at the time) and the CDX appears not to have been updated. At the time of recording, the payment.dbf is opened briefly relying on Fox's ability to lock the table, the record is appended and the table is closed. No one ever sees contention errors.
>
>If I do a report that uses SQL-SELECT and GROUP the result, the next to last item instead of the last item appears in the report for one payor out of the 200. When an aging report is run, the payor looks to be late. When a history is run, the payment is there properly recorded.
>
>It looks like either SQL is broken or updating CDXs is broken from within FoxPro, but I can't believe that either scenario is likely or hundreds of others would have seen this behavior before now. I am out of other ideas.
>
>Any input is greatly welcome.