The statement below gives me the result I would expect should there not be a staff.dbf file. However if there is a staff file it appends all records from the client file. Although there are only 2 records with grp = 1. Any ideas?
IF !FILE("STAFF.DBF"
SELECT DISTINCT Client.sysref, Client.Surname,
Client.FirstName ;
INTO TABLE STAFF ;
FROM CLIENT ;
WHERE CLIENT.GRP = 1 ;
ORDER BY 1
ELSE
IF !USED("CLIENT"
USE CLIENT IN 0
ENDIF
IF !USED("STAFF"
USE STAFF IN 0
ENDIF
SELECT STAFF
DELETE ALL
APPEND FROM CLIENT FOR CLIENT.GRP = 1
ENDIF
IF !FILE("STAFF.DBF"
SELECT DISTINCT Client.sysref, Client.Surname,
Client.FirstName ;
INTO TABLE STAFF ;
FROM CLIENT ;
WHERE CLIENT.GRP = 1 ;
ORDER BY 1
ELSE
IF !USED("CLIENT"
USE CLIENT IN 0
ENDIF
IF !USED("STAFF"
USE STAFF IN 0
ENDIF
SELECT STAFF
DELETE ALL
APPEND FROM CLIENT FOR CLIENT.GRP = 1
ENDIF