Should I use SCAN, Locate or some other method here? 1

[Steve688]

I am working with a table that may have duplicate CONSTITID numbers in it. For the field 'CONSTITID' I need to start by counting how many times the ID occurs and write the count to a table. Right now I am ending up with a count of one everytime, even though I can see duplicate numbers because the table is sorted by that field and not keyed.

        rempsTC.open(":WORK:REMPSSA_WMS_BC.DB")	
	while not rempsTC.eot()
		cid1 =  rempsTC.CONSTITID
		numFound = 0
		scan rempsTC:
		     if rempsTC.CONSTITID = cid1 then
		        numFound = numFound + 1
    		     else
		     endIf
		endScan
		numTC.open(":WORK:NumFound.db")
		numTC.edit()
		numTC.insertAfterRecord()
		numTC."ConstitID" = cid1
		numTC."NumberFound" = NumFound
		rempsTC.nextrecord()
	endWhile
	msgInfo("Done","Done!")

Thanks in advance.

-Steve

 

[TonyMcGuire]

How about

var
  qbe   query
endvar

qbe=query

EMPSSA_WMS_BC.DB | CONSTITID                              |
                 | check calc count all as numberFound    |

endQuery
qbe.executeqbe("numFound.db")

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Steve688]

Thanks Tony.

I did try it with a query and 'calc count all' but I was missing the 'as numberFound' syntax.

That did the trick!

-Steve

 

[TonyMcGuire]

Just so you can check it...

In your initial code you have a while loop.

Within that while loop, you are scanning that same table.

So the scan is going to cause the eot() to result in true after just ONE pass.

That is likely why you only ever got a count of one.




Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Steve688]

Okay, I think I understand. The SCAN has it's own built in While loop.