Report is printing multiple pages

[plynn47414]

I have created a 1 page bar chart. The bar chart prints 5 pages of the same chart and it should only print 1 page.

The report's recordsource calls the following query

TRANSFORM Sum([Fit Gap].RiskCount) AS [The Value]
SELECT [Fit Gap].RiskDecision
FROM [Fit Gap]
GROUP BY [Fit Gap].RiskDecision
PIVOT [Fit Gap].RatingType;

When this query runs I get the following 5 rows of data:

RiskDecision A H L M V
DRMS Accept 11 1 2 5 3
DRMS Delegate/Transfer 4 2 1 1
DRMS Mitigate 5 2 0 3 0
DRMS Watch 7 1 1 3 2
Total Risks 27 6 4 12 5

I also have set the on open property in the report which gathers the data using vb code and puts the data into the fit gap table.

The code looks like this:
Private Sub Report_Open(Cancel As Integer)

Dim MsgContent As String
Dim LinkCriteria As String

DoCmd.SetWarnings False

DoCmd.RunSQL "Delete * from [Fit Gap];"

DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Accept', 'L', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Accept') And (([_Risk Table].RiskRating) In (10,20)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Accept', 'M', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Accept') And (([_Risk Table].RiskRating) In (30,40,60)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Accept', 'H', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Accept') And (([_Risk Table].RiskRating) In (80,90,120)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Accept', 'V', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Accept') And (([_Risk Table].RiskRating) In (160)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Accept','A' , Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RiskDecision)='DRMS Accept');"

DoCmd.RunSQL "INSERT INTO [Fit Gap]( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Mitigate', 'L', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Mitigate') And (([_Risk Table].RiskRating) In (10,20)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Mitigate', 'M', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Mitigate') And (([_Risk Table].RiskRating) In (30,40,60)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Mitigate', 'H', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Mitigate') And (([_Risk Table].RiskRating) In (80,90,120)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Mitigate', 'V', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Mitigate') And (([_Risk Table].RiskRating) In (160)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Mitigate','A', Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RiskDecision)='DRMS Mitigate');"

DoCmd.RunSQL "INSERT INTO [Fit Gap]( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Watch', 'L', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Watch') And (([_Risk Table].RiskRating) In (10,20)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Watch', 'M', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Watch') And (([_Risk Table].RiskRating) In (30,40,60)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Watch', 'H', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Watch') And (([_Risk Table].RiskRating) In (80,90,120)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Watch', 'V', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Watch') And (([_Risk Table].RiskRating) In (160)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Watch','A' , Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RiskDecision)='DRMS Watch');"

DoCmd.RunSQL "INSERT INTO [Fit Gap]( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Delegate/Transfer', 'L', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Delegate/Transfer') And (([_Risk Table].RiskRating) In (10,20)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Delegate/Transfer', 'M', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Delegate/Transfer') And (([_Risk Table].RiskRating) In (30,40,60)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Delegate/Transfer', 'H', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Delegate/Transfer') And (([_Risk Table].RiskRating) In (80,90,120)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Delegate/Transfer', 'V', Count(*)FROM [_Risk Table]WHERE ((([_Risk Table].RiskDecision)='Delegate/Transfer') And (([_Risk Table].RiskRating) In (160)));"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'DRMS Delegate/Transfer','A', Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RiskDecision)='DRMS Delegate/Transfer');"

DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'Total Risks', 'V', Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RatingType)='V');"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'Total Risks', 'H', Sum(RiskCount)FROM [Fit Gap]WHERE (([Fit Gap].RatingType)='H');"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'Total Risks', 'M', Sum(RiskCount)FROM [Fit Gap]WHERE (([Fit Gap].RatingType)='M');"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'Total Risks', 'L', Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RatingType)='L');"
DoCmd.RunSQL "INSERT INTO [Fit Gap] ( RiskDecision, RatingType, RiskCount )SELECT 'Total Risks', 'A', Sum(RiskCount) FROM [Fit Gap]WHERE (([Fit Gap].RatingType)='A');"

DoCmd.SetWarnings False

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open

End Sub

The fit gap table has the following data in it:

RiskDecision RatingType RiskCount
DRMS Accept L 2
DRMS Accept M 5
DRMS Accept H 1
DRMS Accept V 3
DRMS Accept A 11
DRMS Mitigate L 0
DRMS Mitigate M 3
DRMS Mitigate H 2
DRMS Mitigate V 0
DRMS Mitigate A 5
DRMS Watch L 1
DRMS Watch M 3
DRMS Watch H 1
DRMS Watch V 2
DRMS Watch A 7
DRMS Delegate/Transfer L 1
DRMS Delegate/Transfer M 1
DRMS Delegate/Transfer H 2
DRMS Delegate/Transfer V 0
DRMS Delegate/Transfer A 4
Total Risks V 5
Total Risks H 6
Total Risks M 12
Total Risks L 4
Total Risks A 27

My question is why is the report print 5 bar charts that are exactly the same?

 

[Crowley16]

have you got a grouping for riskdecision, and put your chart in the group details section?

--------------------
Procrastinate Now!

 

[plynn47414]

I do have the chart in the group details section of the report. I do not have a grouping on risk decision. I will add a grouping and let you know what the output is.

 

[plynn47414]

I added a grouping on RiskDecision with the following:
Group Header = No, Group Footer = no, Group on = each value,
Group interval = 1, keep together = first detail.

I still get multiple pages. No change

 

[plynn47414]

It looks to me like it is printing the same number of pages as there are in the query output...which is 5. 5 lines of output from the query and 5 pages of the same report.

 

[GavinP]

Hi.

Having just had the same problem, i believe i have resolved it. Finally I can give someone else an answer!

You need to move the graph from the detail into your group header. Once there, the duplicate graphs should disappear, at least they did for me.

Gavin