Hi Bret,
>Perhaps she means that all records with adjacent dates are dupes, even if one is on Friday and the other is on Tuesday. Then it gets more interesting. What
Yes, I meant - my table has an adrress information (ccode+town+street+stnum+stnumext), which should be the same for dupes, and a date of transaction (field date), which for possible duplicates may vary in a particular range, specified by user. By default it's 7 day range.
So, I may try something like:
SELECT *, recno() FROM mytable INNER JOIN mytable AS my ON mytable.pk=my.pk and mytable.address = my.address and between(mytable.date, my.date-7, my.date+7)
This is just guess, I haven't tried it yet.
if you have records for eight consecutive days? How many dupes is that? A SQL solution might involve some sort of non-equi-self-join, something like
>
>SELECT * FROM yourtable INNER JOIN yourtable AS yours1 ON yourtable.yourdate <= yours1.yourdate - 7
>
>Such a query might be slower than an x-base solution.
>
>>To find duplicates by address:
>>
>>select PrimaryKey, Address, count(*) as MyCount from MyTable group by Address
>>
>>If MyCount is > 1... duplicates. To add the condition on dates, write a UDF that returns the number of the week from
>>a date (based on your conditions) and select a new column beased on this new UDF and add this column to the GROUP BY clause.
>>
>>Vlad
>>
>>>Hi everybody,
>>>
>>> I have a problem, which seems to me quite complicated. I need to find possible dupes in my table by address information and the date (date of transaction) should be in the particular range od days, say, within one week.
>>>
If it's not broken, fix it until it is.
My Blog