Show only one mailing lable per address 2

[Evil8]

Sorry to say that I am new to Crystal Reports. I have a Mailing Lable rpt, in Crystal Reports XI, where there are several lables with the same address. I know its possible to have it only have it show one lable per address and I believe it has something to do with grouping and totaling but I haven't been able to find any help yet on this. Does anyone have a solution?

Thanks!
Evil8

 

[synapsevampire]

I the rows are exact supes, select Database->Select Distinct Records.

Otherwise you might group on the phone number or some assured unqiue field and then place the info in the group header and suppress the details and group header.

-k

 

[Turkbear]

Hi,
Are they separate Names at the same location or truly duplicates?
If true duplicates, use the 'Select Distinct Records' option on the report...

Or Group by the Street address ( for instance) and place the label in the detail..




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

That should be:

and then place the info in the group FOOTER and suppress the details and group header.

-k

 

[Evil8]

Again, I'm very new. I've only been using this since last Friday. The Database->Select Distinct Records option is cool and I didn't see it there so thanks for that! But this does not solve this problem.

I'm not sure what you are talking about when you say Suppress Details. I grouped by street address and tried (in turn) to put it in the group header, group footer and the details. Then I right clicked on header/footer/detail
and choose Suppress(No Drill-Down) and I still get four lables per address.

Obviously I am still doing something wrong.

 

[Hillary]

The address grouping must be before another grouping that would "seperate" the records. For example, I use this same type of report for students in a school. I must group the students by address before I group the students by Homeroom.

Let's say a report grouped by homeroom then by address - the address grouping would only work for students who have the same address that are in the same homeroom.

What other groupings do you have on your report?

Hillary

 

[Evil8]

I have no other groupings. So far the best I can do is not to group and use the Database->Select Distinct Records to limit records to one for each person. That gives me two records for each address instead of the four I was getting.

 

[Turkbear]

Hi,
If you get 2 each after the
Database->Select Distinct Records

Then the info is different somehow ..do you want to eliminate the 2und ( or 1st) one anyway?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Evil8]

Yes, I would like to. What is happening is that there are two individuals (Mr and Mrs) entered under one customer number and each individual has two different policy types. Hence I get four address lables for each address without the Select Distict Records. With that selection made I get one lable for each person Mr and Mrs.

I think part of my problem with the grouping is with the Mailing Lable Wizard/Template. I have Details a, b, c, and d(d not having anything in it). Unless I suppress the Group Header & Footer I only get one column of records instead of three.

 

[synapsevampire]

A simpler solution would be to better filter the rows.

Find something unique to each row, such as a row ID, datetime or some such, then in the Report->Selection Formulas->Group place something like:

{table.ID} = maximum({table.ID},{table.groupedByField})

Table.ID being the unique field, and {table.groupedByField} being whatever common denominator field you used to group by, now you can just display within the Details and the report will only display one row for each group.

-k

 

[Evil8]

For this report I am using two tables with a one to many relationship and THE ONE (in this particular case) is not one but two so in reality I end up with a many(two) to many relationship within the Access DB.

The problem is that the unique id was applied to two people (mr and mrs) and it should have been two individual records in the database. I think that because of this it the best I'm gonna get is one lable for Mr Smith and one lable for Mrs Smith.

Thanks though I did learn a lot from this project. I have an on going project to clean up the database and re-train the customer service staff.

 

[synapsevampire]

No, you can get just one row per, the advice is sound, I suggest that you try it and get back with any problems you encounter.

Or if you're just going to ignore the solutions offered, don't bother posting.

Group by something common to the dupes, and then qualify a maximum of some field in the group selection, it can probably even be the saluation (Mr/Mrs), you just need uniqueness.

-k

 

[Evil8]

Sorry, I did try it the first time you suggested it. I tried it again this morning. I tried everything to find a unique field. Each field I tried changed the report record set slightly. Is is possible to use two unique fields in that formula?

{table.ID & table.FirstName} = maximum({table.ID & table.FirstName},{table.groupedByField})

I really do appreaciate your's and everyone's help and I DO NOT ignore any suggestions. I contribute daily in another forum (adobe javascritping) on another website so I understand both sides of this. I will admit I am frustrated with out company database. It was originally set up to work differently than they are using it now so I was brought in to help fix things.

 

[lbass]

Instead of using the label expert, create a new blank report. Group on address (street) (no other groups). Insert three additional group sections so that you have group headers a - d, and then suppress the detail section. Add your fields to the group sections. Then go to the section expert->details->format in multiple columns->layout tab. Set the width, gap, across then down, and then check "format groups in multiple columns". You will have to adjust the page margins (file-page setup) and adjust the width and gap in the layout tab to get the right layout for your labels.

-LB

 

[synapsevampire]

Sorry, you never stated that you'd tried anything or the results.

You can use 2 fields, do so in a formula first, and reference the formula. Although a tableid and a first name formula might not get you there.

-k

 

[Evil8]

Thanks everyone! I learned quite a bit about grouping with CR with this project. This list has been completed and in now emailed off to the HR department.