Trying to Create a Report with No Flow 2

[cghoga]

Greetings,

Please let me agologize in advance as this is probably going to sound vague and/or weird.

It sounded simple at the beginning, but now has me scratching my head.

Basically, I need to create a report that has no flow to it.

I need to place text boxes on a report (representing metric values).

All data needed resides in one table.

For example, the first field on the report will show count of eligible membership.

The next field down will show membership having received a phone call.

It is like each field on the report will probably have a separate control source.

All fields have no association other than they will all be for the same employer group.

Any tips on how to bring such a wide range of values into one report?

I thought about maybe creating a holding table with a column for each metric and then populating each column when the report loads. In the design I can place the text boxes wherever I need to on the report.

Thanks for your help.

 

[dhookom]

You have several choices:
[li]Use multiple subreports which can have their own record sources[/li]
[li]Use domain aggregate functions like DLookup() or DMax()[/li]
[li]Use code to set values of unbound controls[/li]
[li]Use temporary tables to store the values to display[/li]

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cghoga]

Thanks Duane,

I will take a look at these options and determine the one that best fits the need.

Thanks again,
Cary

 

[PWise]

All fields have no association other than they will all be for the same employer group.

If I read your question right in the above line lies the whole key

Create quries for each field

eligiblemembership

Select employergroup, count(*) as counteligiblemembership
from members
where eligible=-1
group by employergroup
phonecall

Select employergroup, count(*) as countphonecalls
from calles
group by employergroup
next field
Select......
from....
group by employergroup


use this sql

Select employergroup,counteligiblemembership,countphonecalls
From employergroups
left join eligiblemembership
on eligiblemembership.employergroup = employergroups.employergroup
left join phonecall
on phonecall.employergroup = employergroups.employergroup
.....

 

[cghoga]

Thanks much for the response.

Would the SQL code go in the control source of each field or would it be included in the On Open event or other?

Thanks again.

 

[dhookom]

What pwise is suggesting is to create a query for your report's record source that is a combination of many other queries joined by EmployerGroup. This would be a fairly efficient method as long as each of the related queries return only a single record each.

Duane MS Access MVP
Now help me support United Cerebral Palsy