Can't suppress duplicate data

[CrystalBox]

I use Crystal XI and ACCESS database.

My problem began when the database was modified to include an additional table. Before the additional table was added a typical report would look something like this: (only one item from the "Allegation" and "Basis" table per case could be selected, as shown below)

Case_No. Emp_Name #1 Emp_Name #2 Allegation Basis

001 J. Doe A. Doe Harassment Age
002 Z. Doe G. Doe Fail to promote Race 003 H. Doe R. Doe Discrim. Gender

Now, that the "Allegation" and "Basis" tables have been modified, I can select multiple items from these two tables. This is where I'm having problem with duplicates. Here's a sample of what the reports look like now:

001 J. Doe A. Doe Harassment Age
001 J. Doe A. Doe Demotion Age
001 J. Doe A. Doe Discrimination Gender

002 Z. Doe G. Doe Fail to promote Race
002 Z. Doe G. Doe Discrimination Race
002 Z. Doe G. Doe Retaliation Race

003 H. Doe R. Doe Discrimination Gender
003 H. Doe R. Doe FailToReinstate Medical
003 H. Doe R. Doe Discrimination Religion

This is what I would like to see on my report:

001 J. Doe A. Doe Harassment Age
Demotion Age
Discrimination Gender

002 Z. Doe G. Doe Fail to promote Race
Discrimination Race
Retaliation Race

003 H. Doe R. Doe Discrimination Gender
FailToReinstate Medical
Discrimination Religion

I tried using the "suppress duplicates" option, but it only is working on the case number.

I appreciate any help you can give me.

 

[dgillz]

Conditionally suprress the other fields with a formula:

Not OnFirstRecord;
If previous({case_no}) = {Case_no} then true else false.

I may have my logic backwards so if the above does not wotk than reverser the order of true and false.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[CrystalBox]

Thanks dgillz, I'll give that a try.

 

[CrystalBox]

Dgillz, I tried the formula on the case # field and it did suppress the duplicates but it also filtered out some of the other info related to the case. I failed to mention that each case could have more than one emp_name1 or
emp_name2

Example:

001 J. Doe A. Doe Harassment Age
B. Doe C. Doe Demotion Age
Discrimination Gender

002 Z. Doe G. Doe Fail to promote Race
X. Doe Z. Doe Discrimination Race
R. Doe S. Doe Retaliation Race

003 H. Doe R. Doe Discrimination Gender
J. Doe L. Doe FailToReinstate Medical
Discrimination Religion
Using your formula on the case no. gives me only one line of information. I need to show one case no. all employees that are involved in the case.

example:
001 J. Doe A. Doe Harassment Age

Thanks

 

[dgillz]

Where di you place this formula? It appears you are suppressing the entire detail section, and you need to suppress onlyy the field(s) you want to have suppressed.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[CrystalBox]

Dgillz, I placed the formula in the detail section using the X-2 button next to the suppress check box. I'm I supposed to insert it in the selection formula section?

 

[CrystalBox]

Dgillz, I guess I'm confused on where to insert your formula. Can you please tell me where the formula should be inserted. Thank You

 

[dgillz]

right click the field you want to suppress conditionally, and select format field. Click the X-2 button to the right of the suppress checkbox and enter this formula there.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[CrystalBox]

Thanks Dgillz. The formula works on the case no field but not on either employee field(emp_name1, emp_name2). I'm still getting duplicates. Could it have something to do with sorting?

 

[dgillz]

If your data looks like what you have posted this should work perfectly. Make sure you are not suppressing any records. If you want to post the report (with saved data) I can take a look at it.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[CrystalBox]

Thanks Dgillz. I wish I could post my report, but but because of confidentiality reasons I can't. I think the problem is in the poor design of our database structure. I think it's the redundancy that's occuring in the "Allegation" and "Basis" tables. Let me explain if I may, for every Allegation, up to 7, you could have 8 Basis.
Now here's is where the redundancy is occuring. Every allegation that is identified has the same exact basis identified with it per case.
Example:

Case No. Allegation Basis
001 Discrimination Age
Gender
Ethnicity
Harassment Age
Gender
Ethnicity
Failure to hire Age
Gender
Ethnicity

This is one case. As you can see, under "Basis" the same three protected groups (Basis) are being repeated. As a test I removed the "Allegation" and "Basis" tables and it seems to fix the duplication problem. I know this is probably a bit too much information for you. Sorry about that. Any suggestions?

 

[lbass]

Try removing the Basis table and placing it in a subreport that is linked on the groupfield (case no), and place it in a group section for case number and set it to underlay following sections.

-LB

 

[CrystalBox]

I'll try that LB.

Coincidentally, I had just inserted a sub-report using the two tables (Allegation & Basis) and linked it to the main report Case primary no, and then I tried Dgillz formula. It partially worked. I was able to remove duplication from the "Allegation" table, but not the "Basis" table.

I'll try your suggestion LB and let you know how it turns out.

Thank you

 

[CrystalBox]

Well LB, it looks like that did the trick. I'm just having problems aligning the sub-report with the detail section of the main report. You wouldn't happen to have a quick fix for that?

First of all I want to thank Dgillz for his help. I learned a new formula that will be useful on some of my other report.

And Thank you LB, you always come through. I appreciate all you help.

 

[lbass]

If you have set the underlay following sections feature and you are having trouble aligning the rows within the sub with the rows in the main report, it really is kind of trial and error. Try to attach all fields to guidelines, and minimize the section heights and then check the match. No other real trick to this.

-LB

 

[CrystalBox]

Thanks Again LB

 

[CrystalBox]

LB, I have another question on the same report. I info that is redundant. The sub-report that I inserted is giving me data that is being repeated. It's not like the duplication that i reported to you above. It's info that we manually repeat. Here's an example:

Allegation(Main report) Basis(sub-report)

Inappropriate Conduct Sex
Age
Medical Condition
Discrimination Sex
Age
Medical Condition

This is example of 1 record. What you see is 2 allegations with the exact basis. The basis will always match each allegation. This is the way it is entered into the database. Is there some way I can show the basis once when there is more than one allegation? My report can get pretty cluttered, especially when I have 6 entries under "basis".

 

[lbass]

Not sure, but if you have linked the sub on case number, and placed it in the case number group section, I think it should appear only once. Don't use the allegation field in the sub. Sort on the basis field in the sub, and check "select distinct records".

-LB

 

[CrystalBox]

Thanks LB. I'm only using the basis for the sub-report. I sorted on the "basis" field in ascending order and it solved my redundancy problem. I didn't see the "select distinct records" option. The only options I was given was "ascending" and "descending".

 

[lbass]

database->select distinct records

-LB