My Report Prints Multiple Copies 1

[bradleyhorn]

Hi Y'all,

I have a brainteaser. My(single line) Access Report prints the single line over and over into several pages. I would have expected it print only one line of one page. The "report design view" shows only one field and a label to print. The "Report Prieview" and printing process adds the single line many times over.

Any suggestions on how to get it to print only once?

Bradley

 

[BobStubbs]

Could you have many identical records in the table on which your report is reporting? I know this sounds daft, but if you had 10 people called 'John Smith', then when you include the 'Name' field in the details section of your report, it will list 'John Smith' ten times.

Or, is your report based on a query which joins tables together? If so, perhaps you are listing a field from the same record on the 'many' side of a one to many relationship, so it will appear many times.

If this doesn't help, perhaps you can post some details of the field / table / query etc, which may help someone to spot the problem.



Bob Stubbs

 

[bradleyhorn]

The one Field in the report is an expression that reads as follows:

=Count(IIf(1=1,1))

The query it "counts from" is a collection customer records which fit a certain criteria. The query is indeed a join of two tables. Interesting to note the Reported "record count" exactly equals the number of times the field is repeated. In this case the record count is 29 and the field is repeated 29 times (over 3 pages.)

Bradley

 

[BobStubbs]

I can create a similar effect, using two tables in my database. I'll explain the tables so you can see the relationship.

tblEmployees contains these columns:
EmployeeID
EmployeeFullname
etc

tblApplicationsUsed contains these columns:
RecordID
EmployeeID
SoftwareLicenceID
Stats1 ...............(I'll explain this later)
etc

I can use a query based on the 'EmployeeID' based relationship between these two tables, to list all software licences for one employee. If someone uses four applications, for example, the report might look like this - I have only shown the important bits, leaving out the report header etc.

Employee ID Header area

    1234567 John Wilson

Detail

    SoftwareLicenceID: Licence A
    SoftwareLicenceID: Licence B
    SoftwareLicenceID: Licence C
    SoftwareLicenceID: Licence D

If I add a calculated text box called 'txtCounter' to the detail area, and include your formula =Count(IIf(1=1,1)), the result is:

Employee ID Header area

    1234567 John Wilson

Detail

    SoftwareLicenceID: Licence A    txtCounter: 4
    SoftwareLicenceID: Licence B    txtCounter: 4
    SoftwareLicenceID: Licence C    txtCounter: 4
    SoftwareLicenceID: Licence D    txtCounter: 4

If this is your reporting problem, try moving the calculated text box to the Group Header or footer. Make sure there is nothing in the Details section, and set its 'Visible' property to 'false'. When I do this in my example report, the result is:

Employee ID Header area

    1234567 John Wilson        txtCounter: 4

Detail
    (now invisible)

An alternative way to do this type of counting, is to include a hidden column in your table (Mine is alled 'Stats1'. This is a byte field, which defaults to the value '1' when a new record is created, and is never changed.

You can list this in queries, and use functions to count and sum it where required.


Bob Stubbs

 

[bradleyhorn]

Solution 1: The use of a default field (value=1) seems simple. The Sum function probably wouldn't create dups like the Count function. I decided to try and try the other solution as it didn't involve changing the tables.

Solution 2: By putting the calculated field in the "group header", do you mean to put the field in the "page header"? When I put it in the page header the identical expression used in the source column gives me an "#ERROR" in the report.

I was able to stop it from repeating by setting the "hide duplicates" property to yes for the calculeted field. This however did no good when applied to a report that had many calculated fields (30-40). I guess I will try putting some of these fields in the "page header" and see how it works.

 

[bradleyhorn]

Bob,

I now understand why the "Hide duplicates" worked for my single line report and not for my longer report. The hide duplicated property hides duplicate rows that appear one after the other as in a sorted group of records.

This does not work for the longer form report that has many lines, all of which are different. This is the problem I am working on now. I still think the header/detail issues may be pertainant here as the number of records returned records equal the number of copies of the report.

Bradley

 

[bradleyhorn]

Final resolution = put the stuff you don't want to repeat in the header or footer of the report. Rookies' mitake.

Thanks for the help!