>>Hi, Is is possible to use an sql statement(s) to count and number duplicates? The original table looks something like this, showing an excerpt where there are three entries for one date:
>>
>>
>>Mdate Vs
>>05/28/2013 v1
>>05/28/2013 v1
>>05/28/2013 v1
>>
>>
>>The following is a start in that it will give me a table that shows how many duplicates exist for each date.
>>
>>
>>SELECT mdate,COUNT(mdate) FROM ch5 ;
>> INTO TABLE dups;
>> GROUP BY mdate ;
>> HAVING COUNT(mdate) > 1
>>
>>
>>Here is a part of the report that the above produces:
>>
>>
>>mdate cnt_mdate
>>05/28/2013 3
>>08/28/2013 2
>>
>>
>>And by using set relationship and a do while loop, I can count and label the duplicates. But I was just wondering if an SQL statement(s) could do it all? The final output that I need is::
>>
>>
>>Mdate cnt/date Vs
>>05/28/2013 1 v1
>>05/28/2013 2
>>05/28/2013 3
>>
>>
>>Thanks,
>>Steve
>
>To add sequential numbers to records, read the section "Including record numbers in results" in
http://www.tomorrowssolutionsllc.com/Conference%20Sessions/Solving%20Common%20Problems%20with%20VFP%27s%20SQL.pdf. Basically, you use one query to collect the results and another to add RECNO() to the field list.
>
>Tamar
Thank you! The part you refer to is on p. 19. But I don't want to insert recno()'s per se. (I shouldn't have skipped at step.) I wondered if I could number the instances where there are multiple occurances for a given date, perhaps resulting in a numbering schema as shown in cnt/date or new_index; because, once I see something like this:
Mdate cnt/date new_index Vs
05/28/2013 1 2013052801 v1
05/28/2013 2 2013052802 v2
05/28/2013 3 2013052803 v3
I need to blank out all but the first of the multiple Vs's for a given date, for a final report that will look like this:
Mdate cnt/date Vs
05/28/2013 1 v1
05/28/2013 2
05/28/2013 3
