Hello all.....

I have code that looks at a table and finds customers with duplicate orders and combines then into one record. The code works fine when one customer has duplicate orders but with multiples it fails. My problem is when I do "do while ! eof(), it runs the next select statement for all the records located in the cursor instead of one record at a time. I guess I needed something like VB where I could do one record then say movenext. This customer can buy either 1, 2, or 3 licenses. The file is then exported to us as txt and loaded into lifprint.dbf

Just in case, here is my code and thanks for your time!

Sunshine




**************************************
* procedure likerecs
* searches lifprint for like records with
* different licenses and combines them.
*************************************
Procedure likerecs

public lic
public cost
public arr

use lifprint

** Find the customers with duplicate records present
select * from lifprint group by dob, lname, fname having count(*) > 1 into cursor dups

rec_count = reccount()
i = 1

** Use the cursor of customers with duplicate records to put all the duplicate records
** into table c:\temp (for each customer).
do while .not. eof()
if i > rec_count
exit
endif
select lifprint.* from dups,lifprint where dups.dob = lifprint.dob and ;
dups.lname = lifprint.lname and dups.fname = lifprint.fname ;
order by lifprint.lic_num;
into table c:\temp ;

&& Take the first record in temp and put into memvar - an array
&& Each array index corresponds to the lifprint table (temp has the same structure).
&& The first field in lifprint = memvar(1) and the last = memvar(44)
USE C:\TEMP
GO top
scatter to memvar

** If there are 3 records, the customer has bought all 3 licenses available.
** Combine those into one record with license = to FOB.
if reccount() = 3

** Add a spot to lifprint for the combined master record use lifprint
append blank

replace lifprint.lname with memvar(2), lifprint.fname with memvar(3),;
lifprint.mi with memvar(4), lifprint.address with memvar(6),;
lifprint.city with memvar(7), state with memvar(8), lifprint.zip with memvar(9),;
lifprint.phone with memvar(10), lifprint.county with memvar(11),;
lifprint.co_res with memvar(12), lifprint.ssn with memvar(13),;
lifprint.race with memvar(14), lifprint.sex with memvar(15), lifprint.dob with memvar(16),;
lifprint.deposit with memvar(17), lifprint.rev_date with memvar(21),;
lifprint.start_date with memvar(31), lifprint.last_date with memvar(32),;
lifprint.st_lic_num with memvar(43), lifprint.rcn with memvar(44),;
lifprint.lic_num with 'FOB', lifprint.lic_cost with 175,;
lifprint.db_lic_arr with ' XXX '

** Delete the three detail records that were combined into one.
delete from lifprint where ((lic_num = 'FF ' .or. lic_num = 'FS ' .or. lic_num = 'FHO');
.and. lname = memvar(2) .and. fname = memvar(3) .and. dob = memvar(16))

use c:\temp
delete from temp where ((lic_num = 'FF ' .or. lic_num = 'FS ' .or. lic_num = 'FHO');
.and. lname = memvar(2) .and. fname = memvar(3) .and. dob = memvar(16))

i = i + 1
endif

** If the customer has bought 2 licenses, we must compare the lic_num to determine
** which licenses were bought.
if reccount() = 2

** Put the lic_num of each record into a variable to easily compare values.
select temp
go top

do while !EOF()
scatter fields lic_num to rec1_arr
skip
scatter fields lic_num to rec2_arr
exit
enddo

** Compare values to assign the correct combo license to the customer
if rec1_arr(1) = 'FF' and rec2_arr(1) = 'FS' && Freshwater & Saltwater
lic = 'FFS'
cost = 120
arr = ' XX '
else
if rec1_arr(1) = 'FF' and rec2_arr(1) = 'FHO' && Freshwater & Hunting
lic = 'FOF'
cost = 115
arr = ' X X '
else
ic = 'FOS' && Otherwise Saltwater & Hunting
cost = 115
arr = ' XX '
endif
endif

** Add a spot to lifprint for the combined master record
use lifprint
append blank

** Insert values into the mater record
replace lname with memvar(2),fname with memvar(3),;
mi with memvar(4), address with memvar(6),;
city with memvar(7), state with memvar(8), zip with memvar(9),;
phone with memvar(10), county with memvar(11),;
co_res with memvar(12), ssn with memvar(13),;
race with memvar(14),sex with memvar(15), dob with memvar(16),;
deposit with memvar(17),rev_date with memvar(21),;
start_date with memvar(31),last_date with memvar(32),;
st_lic_num with memvar(43), rcn with memvar(44),lic_num with lic,;
lic_cost with cost, db_lic_arr with arr

** Delete the 2 detail records
delete from lifprint where ((lic_num = 'FF ' .or. lic_num = 'FS ' .or. lic_num = 'FHO');
.and. lname = memvar(2) .and. fname = memvar(3) .and. dob = memvar(16))

use c:\temp
delete from temp where ((lic_num = 'FF ' .or. lic_num = 'FS ' .or. lic_num = 'FHO');
.and. lname = memvar(2) .and. fname = memvar(3) .and. dob = memvar(16))

i = i + 1
endif

enddo

close databases

return