Comparing two records in query

[manrique83]

I need to know if there is any way to do this in Crystal.

I have a query that is irrelevant to my question, but I need to compare records based on that query.
Here are sample records...

GROUP ID SSN ADDRESS City
111 123456789 4906 October Dr. Chicago
111 123456789 4906 October Dr. Chicago
111 123456789 4700 September Dr. Los Angeles

I need to display the record that doesn't have the same address under the same SSN and same GROUP ID, like the last records shown.

How do I do this?? Please help.

 

[synapsevampire]

Group by the group iD, SSN, Address and City, and then in the Report->Edit Selection Formula->Group place:

distinctcount({table.address}, {table.city}) = 1

Should get you close.

Unfortunately the most relevant information you didn't share, such as the query you intentionally did not post, and your version of Crystal, database used.

With Crystal 9 or later you can paste in your query as the datasource using the Add Command listed under your database.

-k

 

[manrique83]

Sorry about that. I just didn't think the query was relevant but here is the real thing.

SELECT
profile.ssn,
profile.name1,
profile.name2,
profile.name3,
profile.name4,
addr_profile.addr1,
addr_profile.addr2,
addr_profile.addr3,
addr_profile.addr4,
addr_profile.addr_inst,
addr_profile.addr_desc,
addr_profile.addr_supp
FROM
profile,
addr_profile,
dealr
WHERE
( profile.i_dealr=dealr.i_dealr_id and
profile.i_ac=addr_profile.i_addr_ac and )
AND
profile.status != 'C'

I basically want break on any account whose fields under the same SSN don't match.

I dont understand your solution, can you briefly explain what it does?

Also my version is 8.5

Thanks synapsevampire

 

[manrique83]

Also I forgot to mention, I only want to do comparison where the SSN has more than one record.

 

[feli1991]

A solution in CR:
- create a group gr1 = group ID + SSN
- create a secong group gr2 = address+ city+...
- calculate a running total field on gr2 as tot12: count your gr2..evaluate on each record, reset on change of group;
- print ONLY when tot12 = 1, ( you have only one record with gr2) OR tot12 > 1 ( at least 2 recors...) on the footer line of the group ;
If you have other restriction ( more SSN..) it is analogous.

 

[synapsevampire]

If you only want those with a count of one distinct address per ID, then my solution should work for you.

Your query doesn't seem to do this, so I won't get into that, I had thought that you had a query which returned the proper results.

If you want all of the distinct addresses, then use Database->Select Distinct Records

Not sure how I can elaborate on the previous suggestion until you've tried it.

Use Insert->Group to create groups, or create a formula of:

{GROUP ID} & {SSN} & & {ADDRESS} & {City}

and use the aforementioned formula in the Report->Edit Selection Formula->Group

-k

 

[lbass]

If you want to show only those records where there are two different addresses per SSN, then insert a group #1 on {table.groupID} and a group #2 on SSN, and then go to report->selection formula->GROUP and enter:

distinctcount({table.address},{table.SSN}) > 1

-LB

 

[manrique83]

Thanks to all of you!
I will try these solutions and see how it works out.