Need help with duplicate records

[Former Member]

Hi,

I'm using VS 200 and the bundled CR. Also, I'm using SQL Server 2005 CE v3.5 for a database, which is an ADO.NET DB (can't use stored procedures).

I am selecting records from one table based on criteria from another. Here's the selection forumla I'm using:

{tblVisitDates.PlayerType} IN {?PlayerType}

The problem is that I'm getting back duplicate records in the primary table. I only want to see the record form the primary table once, no matter how many matching records there are in the child table.

I saw several other posts on this subject, but most seem to deal with the full version of CR, or specify a stored procedure as the solution. Is there a way to code the selection formula, or to group out duplicate records, in the report itself to solve this problem?

Thanks in advance!

-Will

[Former Member · Accepted solution]

Hi Will,

You can try Below Formula:

Assume that you are getting duplicate records in detail section then:

1. Go to u2018Reportu2019 menu option >> Section Expertu2026

2. Select u2018Detailu2019 section

3. Click on formula(X-2) for Suppress and Write a formula for all the fields present in the detail section using conjunction u2018andu2019

For instance,

{Customer.Country} = Previous({Customer.Country}) and

{Customer.CustomerID} = Previous({Customer.CustomerID}) and

:

:

4. Click OK

Regards,

Alpana

[Former Member · Accepted solution]

I tried including the formula in each of the detail sections of the report (I am creating mailing lists at this point), and it supressed all of the records.

...

Your solution does work- I was applying it incorrectly. First, I was entering the formulas into the wrong Suppres (I used Suppress Blank Section for some reason). That's what caused it to supress all the records.

When I entered the formula into the Supress (No Drill Down) option, it worked. However, I found that if I turn onthe check box, the first record will always be suppressed, for some reason. When I unchecked that box, everything worked fine.

Would anyone be able to explain briefly why these results occured?

In either case, thank you very much for your help!

Edited by: Will Smith on Sep 19, 2008 5:58 PM

[Former Member · Accepted solution]

Hi, Will,

You may try my simple way:

1. For all fields in detail, setup their Format Editors: tick "Suppress if Duplicated".

2. Click Section Expert, click section Detail, tick "Suppress Blank Section".

Then your duplicated line will be suppressed.

But if you have any total value to show, you'll need to use "Running Total Fields" to avoid duplicate summary instead of "Insert Summary" .

~Michelle

[Former Member]

Hi Will

You can use Distinct function to avoid the duplication of records.

Distinct function forces the query to retrieve only unique sets of data.

Hope it resolve your query

Regards

Asha.

[Former Member]

Actually, I am familiar with the distinct function, but not in conjunction with a selection query. I am very new to using CR, so would it be possible to post an example?