Duplicate Data

[mark curtis]

I have a report which is filtered by department. In each department section the fields returning data are project name,spend, invoiced, committed and issues. The problem that I have is that a project may have many issues and my report writes multiple of the fields above for the number of issues I have. I have used the hide duplicates to Yes but this does not provide an accurate report because other projects grouped in the department may have the same spend, invoiced or committed and so the first of many will be shown. I feel that I may need to validate each field against the project name and then hide any duplicates. Any help would be fantastic.

 

[Pat Hartman]

I assume the report is based on a query that joins two tables that have a 1-to-many relationship. Remove the many side table (issues) from the main report's query. Then create a separate query and a sub-report to show the issues.

 

[BSmith]

Pat, (or whoever else wants to answer this)

I have a similar issue and took your advice about building the seperate query and subreport. This works.

But, I want to hide only certain fields of my query if the record is duplicated.

example query return:

job #2 - 12 - 13 - 14
job #2 - 12 - 13 - 14

what I want in my report:

job #2 - 12 - 13 - 14
job #2

 

[Pat Hartman]

Set the hide duplicates property of the controls where you want to hide duplicates.