Printing a report with a chart

W

WildlyHarry

I have a report with a chart based on a query. The chart aggregates query
results. Unfortunately, the report creates a chart and a page for every row
returned in the query, and all of the charts present the same information.
So when my users go to print the chart for thier records, they get dozens of
pages of duplicates. Is there any VBA that I can put behind a button that
would limit the print range to just the first page? Thanks in advance for
you help.
 
G

Gina Whipp

WildlyHarry,

What you would need to fix here is the query that is attached to the chart.
It is obviously returning duplicate information. Open the query and *fix*
the joins OR set the criteria and that will fix your report.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
W

WildlyHarry

The query is working as intended. A serious of transaction volumes are
returned by the query based on date ranges ie

01/01/01 Tran Code 1 50 Transactions
01/02/01 Tran Code 1 35 Transactions
01/03/01 Tran Code 1 70 Transactions

The chart is a line graph the shows the volume trending by date.
Unfortunately it returns a chart for every line returned by the query. For
the above example, it would create a three page report. Any other thoughts?
 
W

WildlyHarry

The query does compute totals. The total number of transactions by date and
tran type. Which is what I want to see. I am graphically tracking system
performance and I need to know what hits each day in order to see the volume
change.
 
G

Gina Whipp

WildlyHarry,

Okay, I must be misunderstanding something you said you get a line for every
transaction as if you are seeing the individual transactions instead of the
totals. But what I am now getting is that it, in fact. does give you totals
for each day. Perhaps it would help if you copy/paste the SQL of the query
that is attached to the graph since you say it gives you what you want.

I am also now confused where the duplicates come into play? I do understand
the page for every row, though I might go for a page every month but that is
just me, but I still don't get why duplicates.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
W

WildlyHarry

SELECT [Step 1 - union all archive transactions].[Prcsng Dt], IIf([Step 1 -
union all archive transactions].[scnro displ nm]='Checks, MIs - Sequential
Number','Checks MIs - Sequential Number',[Step 1 - union all archive
transactions].[scnro displ nm]) AS [Scnro Displ Nm], [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Tran Codes].[Trxn Type2 Cd], [Step 1
- union all archive transactions].[Number of Trans]
FROM [Step 1 - union all archive transactions] LEFT JOIN [Tran Codes] ON
([Step 1 - union all archive transactions].[Trxn Type2 Cd]=[Tran Codes].[Trxn
Type2 Cd]) AND ([Step 1 - union all archive transactions].[Trxn Type1
Cd]=[Tran Codes].[Trxn Type1 Cd])
WHERE ((([Step 1 - union all archive transactions].[Prcsng Dt]) Between
#8/1/2008# And #8/11/2009#) And ((IIf([Step 1 - union all archive
transactions].[scnro displ nm]='Checks, MIs - Sequential Number','Checks MIs
- Sequential Number',[Step 1 - union all archive transactions].[scnro displ
nm]))='CIB - Previous Average Activity') And (([Step 1 - union all archive
transactions].[Trxn Type1 Cd])='1003') And (([Tran Codes].[Trxn Type2
Cd])='DEPOSIT'))
ORDER BY IIf([Step 1 - union all archive transactions].[scnro displ
nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential Number',[Step
1 - union all archive transactions].[scnro displ nm]), [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Step 1 - union all archive
transactions].[Prcsng Dt];
 
G

Gina Whipp

Is this a copy/paste? I'm seeing IIf statements with one two many
arguements?

ie: IIf([Step 1 - > union all archive transactions].[scnro displ
nm]='Checks,
MIs - Sequential Number',
'Checks MIs - Sequential Number',
[Step 1 - union all archive > transactions].[scnro displ nm]) AS [Scnro
Displ Nm],

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

WildlyHarry said:
SELECT [Step 1 - union all archive transactions].[Prcsng Dt], IIf([Step
1 -
union all archive transactions].[scnro displ nm]='Checks, MIs - Sequential
Number','Checks MIs - Sequential Number',[Step 1 - union all archive
transactions].[scnro displ nm]) AS [Scnro Displ Nm], [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Tran Codes].[Trxn Type2 Cd], [Step
1
- union all archive transactions].[Number of Trans]
FROM [Step 1 - union all archive transactions] LEFT JOIN [Tran Codes] ON
([Step 1 - union all archive transactions].[Trxn Type2 Cd]=[Tran
Codes].[Trxn
Type2 Cd]) AND ([Step 1 - union all archive transactions].[Trxn Type1
Cd]=[Tran Codes].[Trxn Type1 Cd])
WHERE ((([Step 1 - union all archive transactions].[Prcsng Dt]) Between
#8/1/2008# And #8/11/2009#) And ((IIf([Step 1 - union all archive
transactions].[scnro displ nm]='Checks, MIs - Sequential Number','Checks
MIs
- Sequential Number',[Step 1 - union all archive transactions].[scnro
displ
nm]))='CIB - Previous Average Activity') And (([Step 1 - union all archive
transactions].[Trxn Type1 Cd])='1003') And (([Tran Codes].[Trxn Type2
Cd])='DEPOSIT'))
ORDER BY IIf([Step 1 - union all archive transactions].[scnro displ
nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step
1 - union all archive transactions].[scnro displ nm]), [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Step 1 - union all archive
transactions].[Prcsng Dt];

Gina Whipp said:
WildlyHarry,

Okay, I must be misunderstanding something you said you get a line for
every
transaction as if you are seeing the individual transactions instead of
the
totals. But what I am now getting is that it, in fact. does give you
totals
for each day. Perhaps it would help if you copy/paste the SQL of the
query
that is attached to the graph since you say it gives you what you want.

I am also now confused where the duplicates come into play? I do
understand
the page for every row, though I might go for a page every month but that
is
just me, but I still don't get why duplicates.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
Click to expand...
 
W

WildlyHarry

Its a copy and paste. The Iff statement is cumbersome, but it is valid.

Gina Whipp said:
Is this a copy/paste? I'm seeing IIf statements with one two many
arguements?

ie: IIf([Step 1 - > union all archive transactions].[scnro displ
nm]='Checks,
MIs - Sequential Number',
'Checks MIs - Sequential Number',
[Step 1 - union all archive > transactions].[scnro displ nm]) AS [Scnro
Displ Nm],

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

WildlyHarry said:
SELECT [Step 1 - union all archive transactions].[Prcsng Dt], IIf([Step
1 -
union all archive transactions].[scnro displ nm]='Checks, MIs - Sequential
Number','Checks MIs - Sequential Number',[Step 1 - union all archive
transactions].[scnro displ nm]) AS [Scnro Displ Nm], [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Tran Codes].[Trxn Type2 Cd], [Step
1
- union all archive transactions].[Number of Trans]
FROM [Step 1 - union all archive transactions] LEFT JOIN [Tran Codes] ON
([Step 1 - union all archive transactions].[Trxn Type2 Cd]=[Tran
Codes].[Trxn
Type2 Cd]) AND ([Step 1 - union all archive transactions].[Trxn Type1
Cd]=[Tran Codes].[Trxn Type1 Cd])
WHERE ((([Step 1 - union all archive transactions].[Prcsng Dt]) Between
#8/1/2008# And #8/11/2009#) And ((IIf([Step 1 - union all archive
transactions].[scnro displ nm]='Checks, MIs - Sequential Number','Checks
MIs
- Sequential Number',[Step 1 - union all archive transactions].[scnro
displ
nm]))='CIB - Previous Average Activity') And (([Step 1 - union all archive
transactions].[Trxn Type1 Cd])='1003') And (([Tran Codes].[Trxn Type2
Cd])='DEPOSIT'))
ORDER BY IIf([Step 1 - union all archive transactions].[scnro displ
nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step
1 - union all archive transactions].[scnro displ nm]), [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Step 1 - union all archive
transactions].[Prcsng Dt];

Gina Whipp said:
WildlyHarry,

Okay, I must be misunderstanding something you said you get a line for
every
transaction as if you are seeing the individual transactions instead of
the
totals. But what I am now getting is that it, in fact. does give you
totals
for each day. Perhaps it would help if you copy/paste the SQL of the
query
that is attached to the graph since you say it gives you what you want.

I am also now confused where the duplicates come into play? I do
understand
the page for every row, though I might go for a page every month but that
is
just me, but I still don't get why duplicates.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

The query does compute totals. The total number of transactions by
date
and
tran type. Which is what I want to see. I am graphically tracking
system
performance and I need to know what hits each day in order to see the
volume
change.

:

What about using a Totals query? This way you don't have the
individual
line items?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

The query is working as intended. A serious of transaction volumes
are
returned by the query based on date ranges ie

01/01/01 Tran Code 1 50 Transactions
01/02/01 Tran Code 1 35 Transactions
01/03/01 Tran Code 1 70 Transactions

The chart is a line graph the shows the volume trending by date.
Unfortunately it returns a chart for every line returned by the
query.
For
the above example, it would create a three page report. Any other
thoughts?

:

WildlyHarry,

What you would need to fix here is the query that is attached to
the
chart.
It is obviously returning duplicate information. Open the query
and
*fix*
the joins OR set the criteria and that will fix your report.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

message
I have a report with a chart based on a query. The chart
aggregates
query
results. Unfortunately, the report creates a chart and a page
for
every
row
returned in the query, and all of the charts present the same
information.
So when my users go to print the chart for thier records, they
get
dozens
of
pages of duplicates. Is there any VBA that I can put behind a
button
that
would limit the print range to just the first page? Thanks in
advance
for
you help.
Click to expand...
Click to expand...
Click to expand...
 
M

Mark Andrews

You need to figure out a way to set the "link child and link master fields"
and use grouping so that the chart only shows once. Think of the chart like
a subreport (if you have experience with those).

If you just throw the chart in the detail section and don't link it you will
get a chart for every record.

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com
 
W

WildlyHarry

I have a subform and form with the same chart. The child fields are bound in
3 places, scenario, tran, and description. Unfortunately, every query record
has the same 3 pieces of data, therefore every record creates chart. I was
hoping to resolve this issue using a report. My original question was
whether or not I could programatically define a print range with something
like a docmd.printout...page 1. Is there any method that would allow me to
do that?
 
M

Mark Andrews

If every query record has the same data, how about putting a groupby level
header and putting the chart in that on both reports? Then you get one
chart instead of multiple.

Don't know the answer off the top of my head on the docmd.printout question
(try the help).
 
W

WildlyHarry

Every record does not have the same data. The records are different based on
date, scenario, type of transaction, and volume. I use the query to return
only the date range, scenario, and transaction types that I want with the
volume aggregated. For example my raw table looks like this

Date1 Scenario 1 Tran 1 Volume 50
Date1 Scenario 1 Tran 2 Volume 45
Date2 Scenario 1 Tran 3 Volume 80
Date2 Scenario 1 Tran 1 Volume 28

My query would return
Date1 Scenario 1 Tran 1 Volume 50
Date2 Scenario 1 Tran 1 Volume 28

I have bound the master and child to scenario and tran and my chart is
produced twice. This is a simplified example. There are 10 scenarios, 200
plus tran types, and 3 plus years of data. And I need each record by date to
show how the volume has trended day to day over time.
 
M

Mark Andrews

It sounds like you understand the concepts pretty well. If your underlying
query produces two records and the chart is in the detail section you get
two charts.
If you want less charts you need to put the chart in a group, header or
footer section and tie it to the report using the link fields appropriately.

I'm not sure what data is on the report and what is on your graph(s) or
exactly what you want as a result. Difficult sometimes without seeing it.

If you can't get it figured out, you could send it to me (use my contact
info on the website).

Mark
RPT Software
http://www.rptsoftware.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Doughnut Chart linked with the slicer in the pivot table 0
Create a chart from a table in word thats dynamic 0
Move Chart with Text 0
How to create a bubble chart with more than 2 variables. 0
Problem with printing a word document containing a table 0
Creating a Chart on form from selected data in query. 0
Page numbering with Merged Document and IF statements 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0

Top