I'm trying to run a report against a customer name and address database. The database has a table for the name (contact) and a separate table for address details. One contact may have more than one address linked to it.
The requirement is to look for duplicate contacts where the postcode firstname and lastname are the same, but the contactID is different, so
Report should show example below (different ContactID, same contact)
ContactID Title Lastname Address1 Postcode
1-60NX5 Mr TekTips 2 TeckTip Rd A1 1AA
1-VG-25 Mr TekTips 2 TeckTip Rd A1 1AA
Report should not show example below (same ContactID, different address - with duplicated postcode)
ContactID Title Lastname Address1 Postcode
1-55R-1 Mr TekTips 2 TeckTip Rd A1 1AA
1-55R-1 Mr TekTips 20 TeckTip Rd A1 1AA
Currently I am using a report with a group selection of
distinctcount({S_CONTACT.ROW_ID},{@Postcode_Lastname_Firstname}) > 1
where @Postcode_Lastname_Firstname} is as the name suggests {S_ADDR_PER.ZIPCODE}&{S_CONTACT.LAST_NAME}&{S_CONTACT.FST_NAME}
This report is pulling back both example above so includes same ContactID with duplicate addresses.
The problem is I think that I am using the ContactID as part of the group selection criteria, but I cannot see how to get round this
Any advice would be appreciated
The requirement is to look for duplicate contacts where the postcode firstname and lastname are the same, but the contactID is different, so
Report should show example below (different ContactID, same contact)
ContactID Title Lastname Address1 Postcode
1-60NX5 Mr TekTips 2 TeckTip Rd A1 1AA
1-VG-25 Mr TekTips 2 TeckTip Rd A1 1AA
Report should not show example below (same ContactID, different address - with duplicated postcode)
ContactID Title Lastname Address1 Postcode
1-55R-1 Mr TekTips 2 TeckTip Rd A1 1AA
1-55R-1 Mr TekTips 20 TeckTip Rd A1 1AA
Currently I am using a report with a group selection of
distinctcount({S_CONTACT.ROW_ID},{@Postcode_Lastname_Firstname}) > 1
where @Postcode_Lastname_Firstname} is as the name suggests {S_ADDR_PER.ZIPCODE}&{S_CONTACT.LAST_NAME}&{S_CONTACT.FST_NAME}
This report is pulling back both example above so includes same ContactID with duplicate addresses.
The problem is I think that I am using the ContactID as part of the group selection criteria, but I cannot see how to get round this
Any advice would be appreciated