How to avoid repeating records in queries

[former_member363950]

I have in my report this report result:

A.......B........C

____________

1.......3..........1

1........4..........2

2........5..........3

1.......3...........1

1........4..........2

I would like in the result to list only one time the same rows. For example here this will be the right result:

1.......3..........1

1........4..........2

2........5..........3

How can i suppress the second or third repeated rows.

I have here CR 8.0 and data from SServer.

Kind regards

Arnaud

Edited by: Arnaud on May 19, 2011 1:58 PM

[Former Member · Accepted solution]

Group report by A.

Move all your data fields into GroupA footer and suppress header and details.

Take care with summaries though, you will need to use Running Totals which evaluate on change of Group A

Ian

[Former Member · Accepted solution]

Actually, to keep with the example you would need to do the following...

1) Create a formula that concatenates A & B

ToText({TableName.A}, "0") & ToText({TableName.B}, "0")

2) Group by this formula.

3) When the group is created, delete the that is automatically generated.

4) Place all of your fields in the group header... not the group footer.

HTH,

Jason

[Former Member]

Hi,

Are you pulling from two different tables, or is this all from one table? Depending on what your trying to do there are different options here.

If it's from different tables you probably should/need to fix the join. If you really just want to see unique records there is the function under DataBase that says (only select unique rows). This will only show unique rows on your report.

Good luck!

[former_member363950]

Hi rody,

I get data from different tables that are join together in database assistent.

Is there a way to customize the joins so that rows can be with the same data.

Kind regards

Arnaud

[Former Member]

Hi,

So in database expert you should check the 'links' tab and ensure the joins are setup correctly between all of the tables used in the report. If the links or joins dont exist at all then you can get a 'cross join' that would produce duplicate data.

Of course it also may just be that your not pulling the column into the report that makes the data unique so although it appears to be duplciate it is actually unique (it just doesn't appear that way).

If you have links setup properly and your still seeing duplicate data then I would probably do a sanity check with the DBA & make sure that's what I should be seeing.

Good luck!