Group Selection problem

[sdzlkds]

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

 

[CoSpringsGuy]

Although I am sure thats a typo... the above would come back because the address is defferent.. 20 Teck Tip and 2 Teck Tip.
Because your detail records dont show the last name and first name .. I wonder if the records that are showing could be because of spelling issues.. Jones in one and Jnoes in another. I would add your formula in the detail section and run the report so you can see exactly what is not matching.

You might also try to to get rid of erroneous spaces in your {@Postcode_Lastname_Firstname} formula:
//
Trim({S_ADDR_PER.ZIPCODE)}&trim({S_CONTACT.LAST_NAME})&trim({S_CONTACT.FST_NAME})


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

I agree, but I wonder also whether you also might be seeing the effects of different cases used in one or more fields, so you could change the formula to:

Trim(ucase({S_ADDR_PER.ZIPCODE}))&trim(ucase({S_CONTACT.LAST_NAME}))&trim(ucase({S_CONTACT.FST_NAME}))

-LB

 

[sdzlkds]

Thanks for the feed back. I don't think I've explained the problem very well. The report is returning data as below

1-60NX5 John TekTips 2 TeckTip Rd A1 1AA
1-VG-25 John TekTips 2 TeckTip Rd A1 1AA
1-55R-1 Fred TekTips 5 TeckTip Rd A2 1AA
1-55R-1 Fred TekTips 5 TeckTip Rd A2 1AA

I want the report to show the first two rows because this is a duplicate contact record (as each row has a different contactID 1-60NX5 and 1-VG-25).

I don't want to show the third and forth rows because this is the same contact record (as each row has the same contactID, 1-55R-1). It appears twice because two (duplicate) addresses have been saved against the same contact record. So, these are duplicate addresses but not duplicate contacts - confusing? Yes!

 

[lbass]

We understand what you are trying to do, and your group selection formula should work correctly--so this means that there is a problem with your Postcode-LastName-FirstName formula. I think you should report back with some real data so we can really see what's going on with your group formula.

Alternatively, take a look at the group headers and notice whether there are "extra" groups that should really be collapsing into one and note how the two values differ.

-LB