Complex Report

  • Thread starter Thread starter Christy
  • Start date Start date
C

Christy

I have a report based on a query. I want to show a value
from a different report (and different query) on the same
report. How can I do that? Should I try to have one query
do both?

Another question:

In the Category Header on the Report, I have calculations
that go with each record in the group. How can i show only
ONE value for ONE of the records (and not all of them)?

Hope this makes sense. I'd be happy to send the document.
Thanks
 
OK for the first Q.

Add a subreport to the main report linking one or more like fields between
the reports. This is called the master (main report) and child (sub report)
link. You could also make a 3rd query that joins the first two queries and
use that third query as your report's record source.


2nd Q.

I assume there are duplicate records for the fields in the Category Header.
To hide the duplicates, just right click on each field and click on
properties and select 'Hide Duplicates'

help?
 
RE: your answer for question 1:

I want to show in my report footer, the value in a
DIFFERENT report header. . I want the sum of a number of
hours for a partcular group, and only the report
header/footer can calculate that.

How can I make the report show a value from a different
report header? (Via a subreport or otherwise)


For question 2:

There are no duplicates in the category header that the
report shows. There are a lot of duplicates in the
database that the report makes its calculations on.

So, I want the report to show this:

XX YY ZZ (page header) to show this: XX YY ZZ
1 a D (category header) 1 a
2 b E 2 b E
3 c F 3 c

Possible?
Please help! Thank you so much!
 
Q1:

1) Use a subreport by linking it to the main report on a field common to
both the main and sub-report. Then make a field in the main report's footer
that uses the field in the subreport as a control source. OR 2)You can
actually have a main report with no control source if you wish. I've done
this for a database which I have to invoice about 20 different treatments
and on the main report footer I show total area treated and total $$ for
reimbursement. The main report takes the sums of the areas and $$ from the
subreports and sums them at the end of the report.
This sound familiar to your situation? There is a icon you click in design
view to add the subreports and you can size them as you wish.

Q2

I don't follow your explanation. Because if D<>E<>F why supress D and F from
your report? Only way to suppress those values would be to include and IIF
statement to filter them from the report. Or use code to suppress them,
which I avoid.

IIF([field1]="D" or [field1] = "F",null,[field1])
returns null if the field equals D or F

IIF([field1]<>"E" ,null,[field1])
returns null if the field is not equal to E
 
1) Ok, add a text box field to the main report footer that references the
subreports field by setting this field's control source equal to the name of
the subreport field.

control source =report![subreport_name]![subreport_fieldname]

You say the subreport doesn't return the same record or result as it does
when embedded in the main report? Then its using an expression to make a
calculation and not a record in a table. If you do the above then the result
has to be the same.


2) Use the IIF function in an expression to specify a condition and a
result for the field in the main report

IIF([category_name] = <some criteria> , <set to this result>, <or this
result>)

Do you want to seperate your data in the report by category? and suppress
any duplicates?

Ex:

Category Class Result
A
1
a
a <<----set 'Hide Duplicates'
b
c

make a Grouping for Category, one for Class then use Detail section for
results, you won't get everything on one line unless someone has some
special code to share.


christy said:
1) OK, sorry if I sound like a 2 year old, but
explain "make a field" in the report footer. Are you
talking about another text box? The subreport is there
now, but it doesn't show the same value that it does when
I run the subreport as a report by itself.

2) D E and F are different RANDOM numbers. They are not in
-----Original Message-----
Q1:

1) Use a subreport by linking it to the main report on a field common to
both the main and sub-report. Then make a field in the main report's footer
that uses the field in the subreport as a control source. OR 2)You can
actually have a main report with no control source if you wish. I've done
this for a database which I have to invoice about 20 different treatments
and on the main report footer I show total area treated and total $$ for
reimbursement. The main report takes the sums of the areas and $$ from the
subreports and sums them at the end of the report.
This sound familiar to your situation? There is a icon you click in design
view to add the subreports and you can size them as you wish.

Q2

I don't follow your explanation. Because if D<>E<>F why supress D and F from
your report? Only way to suppress those values would be to include and IIF
statement to filter them from the report. Or use code to suppress them,
which I avoid.

IIF([field1]="D" or [field1] = "F",null,[field1])
returns null if the field equals D or F

IIF([field1]<>"E" ,null,[field1])
returns null if the field is not equal to E




RE: your answer for question 1:

I want to show in my report footer, the value in a
DIFFERENT report header. . I want the sum of a number of
hours for a partcular group, and only the report
header/footer can calculate that.

How can I make the report show a value from a different
report header? (Via a subreport or otherwise)


For question 2:

There are no duplicates in the category header that the
report shows. There are a lot of duplicates in the
database that the report makes its calculations on.

So, I want the report to show this:

XX YY ZZ (page header) to show this: XX YY ZZ
1 a D (category header) 1 a
2 b
Click to expand...
Click to expand...
E 2
b E
.
Click to expand...
Click to expand...
 
Back
Top