Duplicate Records Query

[BlockFunk]

Hi All,

I've got a bit of an odd problem that someone out there must have encountered before...

I get weekly MIS tables containing customer numbers (each customer has a customer record number) and a "contact reference number" that refers to a record on the system containing detailed info on the contact, plus the date and a system generated week number.

What I want to do is find, for the previous week, the repeat contacts over the last month or 28 days and have some sort of indicator set against them (e.g. "Repeat Contact").

What I've done first off is gathered the data into two tables, one for the last week and another for the previous month and matched one against the other to find duplicates. This is OK, but it doesn't find any from the most recent week (the obvious weakness).

What I need is a way of quering a complete table of 5 weeks data to find all the duplicates, i.e. the recent ones and the ones over the last few weeks but using the previous weeks data as the big question is what proportion of the contacts received over the last week were repeat contacts in the last month?.

 

[Norie]

I'm not quite sure what you are after.

Could you give an example of your data?

 

[BlockFunk]

Hi,

The data is like so...in a single table right now but can be split by the week number into separate tables. The find dupes wizard does find dupes of course but is not the proper solution as some manual rework is still required.

Customer No. Contact No Week No Date
123456 2213485 49 11/3/2005
257249 13849159 49 11/3/2005
321459 241718 49 10/3/2005
138423 3913040 45 12/2/2005
123456 1190344 46 17/2/2005

The data set will only go back five weeks max so that the current week (week 49 in this case) is the week that I'm looking to see contains dupe contacts in the last month or so.

 

[djl0525]

Have you looked at the Duplicate Query Wizard?

 

[BlockFunk]

The query wizard won't perform this task unfortunately, it's a bit more complex

 

[Norie]

What result would you expect from your example?

 

[Norie]

What result would you expect from your example?

I think you should be able to do this without splitting the table.


SELECT Contacts.CustomerNo, Contacts.WeekNo, Contacts_1.WeekNo
FROM Contacts LEFT JOIN Contacts AS Contacts_1 ON Contacts.CustomerNo = Contacts_1.CustomerNo
WHERE (((Contacts.WeekNo)=49) AND ((Contacts_1.WeekNo) Between 45 And 48));

 

[BlockFunk]

Hi,

This would be the kind of thing...

Customer No. Contact No Week No Date
123456 2213485 49 11/3/2005
257249 13849159 49 11/3/2005
321459 241718 49 10/3/2005
138423 3913040 45 12/2/2005
123456 1190344 46 17/2/2005

The ideal output would be

Customer No Repeat Contact?
123456 1 Repeat contact (in 28 days)

 

[Norie]

Did you try the SQL I posted?

I used it on what you posted and it gave the required Customer No.

I'll have think about getting the count.

SELECT Contacts.CustomerNo, Count(Contacts_1.WeekNo) AS CountOfWeekNo1
FROM Contacts LEFT JOIN Contacts AS Contacts_1 ON Contacts.CustomerNo = Contacts_1.CustomerNo
GROUP BY Contacts.CustomerNo, Contacts.WeekNo, Contacts_1.WeekNo
HAVING (((Contacts.WeekNo)=49) AND ((Contacts_1.WeekNo) Between 45 And 48));

 

[BlockFunk]

Thank You, that did the trick and got me thinking of further refinements!!