Report generates too many times

[theKruser]

I have a report with a sub-report that lists incidents. Basically, when an employee has an incident, it is recorded in one table. The report is built off the main personnel table and the incident table through a select query.

The problem is this, the report runs fine and displays everything, but it generates a page for every incident. Hard to explain, but this is what it does:

Suppose Employee A has three incidents. The report displays:

Employee name, employee number
Incident 1 date, time, and description
Incident 2 date, time, and description
Incident 3 date, time, and description

The problem is, it generates the same page the same number of times as there are entries. So, the above is generated exactly identical on 3 different pages, so when I print I get two duplicates.

The tables are related via employee number. Name and number is in the header of the report. Incident data is in the body. Tables are configured as shown below:

tblEMPLOYEE
employee number (primary key)
employee name

tblINCIDENT
employee number
incident number (primary key)
incident number
date
time
details

Thanks in advance for any help. It is much appreciated!

 

[vbaInet]

Are you grouping by Incident Number? If you are click the Incident Number group header and check the Force New Page property.

 

[theKruser]

I am grouping by employee number. I need a printout of each employee with a complete listing of incidents. Do you think I should group by incident number? I will give it a try. Thanks!!

 

[vbaInet]

Actually no

Is your Subreport linked via the Source Object?

 

[theKruser]

Confused. Source object?

 

[vbaInet]

1. In design view, click the Subreport ONCE
2. Go to the PROPERTY SHEET and under the DATA tab look tha values of:
* Source Object
* Link Master Fields
* Link Child Fields

 

[theKruser]

Source: Report.subCHRONO_FILL (name of the sub-report)
Master: Empl#
Child: Empl#

Does that help?

 

[vbaInet]

I think it's your query and not the report. When you run the query you get duplicates don't you?

 

[theKruser]

Why, yes...yes I do. One listing of Empl# and Empl name for every incident.

 

[vbaInet]

So your main report should not be returning duplicate employee names. Remember that the incident fields doesn't need to exist in the underlying query of the main report since that field is already displayed in the subreport. All that needs to be present is the Employee fields plus the incident number (because that's the field that links to the subreport).

Once you've done that, go to the sql view of the query and put in the DISTINCTROW predicate like this:

SELECT DISTINCTROW ....

Look at the query to ensure there are no duplicate rows. If that doesn't work, use DISTINCT.

 

[theKruser]

I really appreciate your help. However, I tried the above steps. Same result.

Removed incident info from main report. Tried both DISTINCTROW and DISTINCT in the SQL view of the query. Still does the same thing.

Main report is linked to sub-report via Empl#, not incident number. Could that be the problem?

 

[vbaInet]

On second thoughts, I've just had a second look at your tables and I think we should digress.

It would depend on what report you want to display:

1. All employees whether or not they HAVE or DON'T HAVE incidents.

OR

2. Only those with incidents?

 

[theKruser]

Actually, the query is tied to a print yes/no option. However, the report should return employee regardless if there has been an incident or not.

 

[vbaInet]

If that's the case, then base your MAIN REPORT on the EMPLOYEE table ONLY. There will be no duplicates in that case.

 

[theKruser]

You are an absolute genius! That worked like a charm. Thank you so very much for your help.

 

[vbaInet]

Grande! Glad I could help