>Hi everyone,
>
>Here is my problem:
>
>I want to find when duplicated records are mistakenly created by my application.
>
>So I plan to check my database before the user gets into the application and after he/she exists.
>
>The database contains employee schedules. Each record contains the position number (PosNum), the date of shift (Djh) and a number indicating which day was used to generate that particular record (Njh). I have about 50,000 records.
>
>And this is the SELECT statement:
>

>    SELECT cHorPosNum, dHorDjh, nHorNjh, COUNT(*) AS CNT ;
>      FROM Horaire ;
>     GROUP BY cHorPosNum, dHorDjh, nHorNjh ;
>    HAVING CNT > 1
>

>
>The combinaison of cHorPosNum+dtos(dHorDjh)+str(nHorNjh,3) should be unique.
>
>I already have the following index tags:
>DHORDJH,
>CHORPOSNUM,
>CHORPOSNUM+DTOS(DHORDJH)+STR(NHORNJH,3)
>
>On a my pc it takes about 1.5 seconds to complete
>for my users are on a 10Bt network it will be longer but acceptable
>but for other users who are on a RNIS network it may be too long.
>
>Does anyone have an idea on how I can optimize this search.
>
>Remember this this is fpw 2.6 code.
>
>Thanks in advance

I would add a where clause to your statement. Do you care about shifts in the past? If not, where (Djh) >= date()-15. Include a new index on the date so it will be optimized and you should get much better results.

The other issue is why or how are the allowed to dupe in the first place? You may find that re-examining your valid on your data entry screen is in order?

My guess is that they pick the employee from a pick list of some sort and then add the date? So vaild on the date should say that emp is already entered for this day?