one query - many reports

[patti]

I need to have several individual reports that come from the qryHospSvcFreq.

Field: HospSvc

I need one report that shows: ONC, RAD

another needs: OB, GYN, NWB

another: CRI, EMD

another to show all records excluding above.

and the director may decide he wants to separate out more services.

I know how to do this manually within the query by changing criteria; but
these reports are run by end users and i need to just them to click on report.

How do i assign parameters to report and have that as basis for query?
currently, the record source is the query.

 

[Evi]

Would you be allowed to use a multi-select list box in a form to open the
report?
Evi

 

[patti]

possibly. would the multi-select be for the field or for the report? can you
clarify.

i am thinking perhaps of creating some small tables then new queries with
table left join to current query and then assigning that as record source.

i can see lots of ways to go but what is most efficient, most logical, most
common?

 

[Evi]

I'm assuming that ONC, RAD etc are a list of items in a table which are also
in a report (they aren't field names, are they? If they are, then that's a
very different story)
A Multiselect listbox, based on that table, whould let you choose the items
you wished to include in your report. You would then click a button on the
form to open the filtered report It just depends what you think your users
would find easiest. An advantage of a combo or list box means that users
won't accidentally type in the wrong letters and they will instantly see all
the choices available

When you say that you are thinking of creating small tables, you do mean
temporary tables, don't you?.

Evi

 

[patti]

Thanks for the help.

ONC, RAD etc are items in the list. The field name is HospSvc.
I have over 50 items in the list so i don't think the multi-select for the
field is the way to go.

I was thinking small tables. tblReport1 would only list ONC, RAD. tblReport2
would have OB, GYN, NWB. and so on. Then new queries left join those tables
to present query .... and use each as report record source.

What i have done for now is just made copies of each query and report for
the necessary set of items.

My thinking is that i should be able to have just one report and one query
and then somehow code parameters....

Much thinking to go.

 

[John Spencer]

You might change your structure to add a table that contains groupings
of the Hospital Services(?) that you are using in the report.

So you would have something like
ONC GroupA
RAD GroupA
OB GroupB
GYN GroupB
NWB GroupB
XXX GroupX
XYX GroupX
....

You could even have one service in multiple groups if needed.

You would add this new table to your query and allow people to select
the Group to limit the report.

Personnally I would probably add one more table that listed all the
grouping names just to ensure accuracy on the group names and to
simplify creating my groups.

You might (I wouldn't) have an ALL SERVICES group where every service
that you have was added to the ALL Service group.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[patti]

thanks, john.

i am probably creating another table and query and then filter my reports.