Multiple queries feeding into Report

[KW99]

Ok here is my problem (not sure if this is easily solved by a query or report, so I posted it in the query section):

I have designed a database amongst other things to calculate client investment values based on price, units etc and criteria, such as date which is chosen by the user from a form. This all works fine.

What I would also like to do is to put some benchmark data for the date chosen in the footer of the reports.

e.g. if the report read:

Fund A, Date 01/01/2009, 1000Units, £1.50, Fund Value £1,500
Fund B, Date 01/01/2009, 2000 units, £2.00, Fund Value £4,000

I would like to add some standard data to the footer of this report which will be reference data based on market averages eg:

UK Equity 12%
US Equity 20%
Property 9%

This footer data will be the same for every client at the same date. The only way the footer data will change is with a change in the date chosen (I have set this query up with a link to a form to choose the date).

The problem is I cannot seem to link the two queries correctly (as they have no linked characteristics other than the date) and I believe that you can only have one query per report.

Is there an easy way to accomplish this?

Thanks in advance.

 

[jjturner]

Hi there,

If it's not feasible to include the benchmark data into your query, you could always build a separate report for just the benchmarking, then nest it as a subreport within your main report footer.

I'm a bit rusty with reports, but that would be my first thought . . .

Hope this helps.

Regards,
John

 

[KW99]

Thanks for this, I actually just nested the query in the report.

I am not sure why I did not do this in the first place.

Thanks for your advice anyway.

 

[just_a_guy]

While you are allowed only one "qery" per report,
that query can "join" (use) more than one table.

Here is an example:

select
rpt.*
, bnch.line_1
, bnch.line_2
, bnch.line_3
from
your_report_table as rpt
left join
bench_mark_data as bnch
on rpt.report_date = bnch.bench_mark_date,
where .....

( Sorry but this page eliminates "unnecessary" blanks,
so you cannot see the indentation that makes
the SELECT statement more readable.

To use this SELECT statement, save it in the Query section of
the database under whatever name you choose (e.g. report_with_benchmark); then substitue the name of that
query for the current source for your report.

The one detail that you must know is the "on rpt.report_date = ..."
must be the same date that you match (compare) to the users
specified date. Otherwise the bench mark data could change
with each row of the report.

Finally, in the "footer" section of the report you just reference
the new colums that come for this new source, just as if
they were apart of the original table.

Does this make sense?

Hope it helps.