Removing duplicates

[rockman20]

I am running CR 8.5 pulling data from SQL 2000 through ODBC.

I have a set of mailing labels that is pulling information from a consolidated file. This file is ASTU4D50. In this field I have....

Family Number
Parent Name
Address
City, State Zip

I have suppressed the Family Number because I do not want that to show on the labels. The reason why I have that in there is because I need to use this as a Unique field. I may have 4 Jane Doe's, one with 2 children in her family and 2 that have single children. (Jane Doe will show up for each student in my ASTU4D50 file)

Here is an example of what the labels would look like

5554422
Jane Doe
4860 Makebelieve Dr
Fargo, ND 58104

5554422
Jane Doe
4860 Makebelieve Dr
Fargo, ND 58104

5553311
Jane Doe
500 Made Up Rd
Fargo, ND 58102

5552200
Jane Doe
205 Makeshift Lane
Fargo, ND 58103

You can see that the first two Jane Doe's are actually the same person because the Family Number is the same. So, how do I keep that second lable from printing by using the Family Number?

Thanks in advance!

 

[skries]

I'm not sure if you tried this, but you can right click on the individual fields and select Format Field, then under the Common tab check the suppress if duplicated box.

If this leaves you with a blank label you can the go to the Format Section and check the Suppress blank section box.

Hope this helps

 

[rockman20]

Here is where I run into an issue. With 22 different databases and well over 100 different people entering in information, the addresses might not be the same.

So it might look like

5554422
Jane Doe
4860 Makebelieve Dr
Fargo, ND 58104

5554422
Jane Doe
4860 Makebelieve Drive
Fargo, ND 58104

If I were to suppress if duplicate, I would get one label like this

5554422
Jane Doe
4860 Makebelieve Dr
Fargo, ND 58104

and another one like this


4860 Makebelieve Drive

If I knew that all of the addresses were entered in EXACTLY the same, I wouldn't have to worry. But this is not the case. But I do know that the family number will be the same because I made the family number their phone number without the area code. So if I can suppress Details b, c, and d if details a is the same, that is what I am looking for.

Thanks!

 

[pgtek]

hi
create a group on family number and in there add the
Parent Name
Address
City, State Zip
in that group

cheers

pg


pgtek

 

[skries]

It sounds like you have a database that allows the entry of various addresses under the record identifier. But the record identifier (family number) does not have to be unique or is it? Is there a unique identifier that you can key off of?

Is there a parent table that keeps just the information for that one family number? How is it you would know which address to suppress or which is correct? I know I am getting off the subject but it might help to know how the data is structured in the tables so that you would know how to layout your report.

Try this in the suppress section of the formatting section

whileprintingrecords
{family number} = next({family number})

 

[rockman20]

Here is a better picture of the data.

We are a school district with 22 different schools and a district office. Each school has its own SQL database and so does the District Office. (23 total databases) Each school database contains tables like ASTU4XXX, APRN4XXX, etc, etc. The only thing that changes is the XXX's which represent the school number.

In the tables, you can enter anything you want for both the address and the family numbers. However, we run a query that takes the home telephone number, strips the area code and the dashes, and makes that the family number. (The telephone numbers SHOULD be unique)

Okay, so now we have the data at each school. Our student information system that writes and reads the SQL data has a job that runs nightly. This job goes out and takes tables like the ASTU4XXX from each school and uploads them into the District copy so we basically now have one database with a mirrored copy of each school. Then we can run a job within the district that consolidates the data from all of the ASTU4XXX tables and makes one large table with all of the information in it. This is what I am pulling my report from.

As for which address to suppress, that is unknown. Basically the report should pull the first instance that shows up. As far as I know the mail should go to the correct address it is just that the verbage may vary slightly in how they do a DRIVE or a DR or a STREET to a ST or so on and so forth.

The setup is kind of confusing, so let me know if I can help clear the picture up some more. I appreciate all of the help!

 

[kskid]

Is there a date field that identifies when the record was added? For example, on our address file, we have a field called creator_date or when the record was created.

Within each parent_ID group, perform a summary and select maximum date.

 

[rockman20]

There is a creation date, but we can't pull from that either. One school could of been on the ball and got these kids enrolled that day and another school could of taken a day or two to enter them in. (The joys of working in a school district where there is no control! We have been running with no control for a long time now. I am really working at cracking down on these secretaries and trying to make them get this information in correctly and on time. Then I won't have to worry about all of this "stuff"

Thanks!

 

[GJParker]

Create a group on family number and place your fields in the group header.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[kskid]

I think you have something there.

Once I inadvertently placed some detail information into the group header and noticed that I did not get all the rows for that detail field, just the first one. It also changed depending on how I sorted the detail records.

So using the same logic, create a group by Parent ID and insert multiple sections for each additional line

GroupHeader 1
GroupHeader 1A (Suppressed) Parent_id
GroupHeader 1B Parent Name
GroupHeader 1C Address
GroupHeader 1D City, State, ZIP
Details (Suppressed)
GroupFooter (Suppressed)

 

[GJParker]

There is no need to create multiple Group header sections all of the details fileds can be placed in Group Header #1

Group Header #1 Parent Name
Address
City, State, Zip
Details (Suppressed)
Group Footer #1 (Suppressed)



Gary Parker
MIS Data Analyst
Manchester, England

 

[kenhamady]

The problem with using the GH to locate the fields is that you have to recreate the label size. If you used the Mailing Label Expert the label is automatic. Another option is to leave the fields in the details (where CR put them) suppress the GH and GF completely. Then create a running total field that resets with each group. Suppress all details where the Running total is not 1.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com