Saving a Report Format

[Guest]

Hi,
I work for an agency that is responible for funding certain public health
programs in several states. I have designed a form in access with several
fields designed to capture different elements of every intervention the
states are using. One intervention is captured per form. I now need to make
a summary of the interventions specific to each state, which I have done for
one state using a report. I created this report through a query for all
forms specific to this state.
Here is my question:
It took a long time to arrange all the fields in a manner acceptable to the
needs of access. As the reports for the rest of the states will look
identical in format, I was hoping that there was some way to save this format
that I worked so long in designing and reuse it for all subsequent reports.
Thank You

 

[guido via AccessMonster.com]

If your subsequent queries have the same field names, you can copy the report
and assign the new query to it.

 

[Guest]

Since all the state reports will have identical formats you only need one
report which you have already completed. The only thing left to do is sort
the data that goes to the report.
There are several ways to accomplish this:

Common to ALL of these is to set the Record Source of the report to the
query that sorts the records.

- - You can set a field up in your query that prompts the user for the state
which will sort all those state records and set.

- - You can also build a filter and use it in the OnOpen event of the report
(a good example is on Allen Brown's website @ http://allenbrowne.com/tips.html

HTH
scuffy

 

[Guest]

Thanks to both of you for getting back to me. Now I have a stupid question:
how exactly do I do what you have suggested? I wasn't sure how to get to the
info on Allen Brown's website. I think my best bet is to copy the report and
assign a new query to it, but how do I go about doing this? I've been
fiddling around with access and I'm not getting very far.
Thanks

 

[Guest]

I am going on the assumption that you have a 2 field table with state names
in it and a state code or number. How do you want to select the state?
From a combo box (drop down list) or entering it?.
Either way the first 3 steps will be the same.

In your query you are using for the report do the following:
1) in an empty column select the table that contains the State field from
the drop down box in the Table row
2) select State from the drop down box in the Field row
3) select Where from the Totals row drop down box

To prompt for entering the state.
4) in the Criteria row type in [Enter State]
5) Open the Report in Design mode and type =[Enter State] as the
ControlSource for the State field.

When you run the report it will prompt to enter State and will produce a
report for that state.
------------------------------
If you are selecting from a Combo box.
4) in the Criteria row type in
=[Forms]![frmFormNameContainingComboBox]![cbxComboBoxName]![Enter State]
5) Open the Report in Design mode and select State as the ControlSource for
the State field.

Your query will use the State selected in the Combo box as the State for
sorting the records for your report.

I hope this isn't too complicated...if it is I can send you an example.

HTH
scruffy

 

[Guest]

I'm sorry. I don't think I explained my problem well enough. I don't have a
two field table. I know how to run a query and select for a certain criteria
to base the query on. My problem is, I hope, pretty simple. When I create a
report from a query, I select the fields from the field list and arrange them
in a certain order and style that makes sense. Since I am using this same
layout for multiple states, I would like to be able to save the way I have
designed the layout for the report and use this version for all the other
states. I hope this is clearer. Thanks for your help.

I am going on the assumption that you have a 2 field table with state names
in it and a state code or number. How do you want to select the state?
From a combo box (drop down list) or entering it?.
Either way the first 3 steps will be the same.

In your query you are using for the report do the following:
1) in an empty column select the table that contains the State field from
the drop down box in the Table row
2) select State from the drop down box in the Field row
3) select Where from the Totals row drop down box

To prompt for entering the state.
4) in the Criteria row type in [Enter State]
5) Open the Report in Design mode and type =[Enter State] as the
ControlSource for the State field.

When you run the report it will prompt to enter State and will produce a
report for that state.
------------------------------
If you are selecting from a Combo box.
4) in the Criteria row type in
=[Forms]![frmFormNameContainingComboBox]![cbxComboBoxName]![Enter State]
5) Open the Report in Design mode and select State as the ControlSource for
the State field.

Your query will use the State selected in the Combo box as the State for
sorting the records for your report.

I hope this isn't too complicated...if it is I can send you an example.

HTH
scruffy

Thanks to both of you for getting back to me. Now I have a stupid question:
how exactly do I do what you have suggested? I wasn't sure how to get to the
info on Allen Brown's website. I think my best bet is to copy the report and
assign a new query to it, but how do I go about doing this? I've been
fiddling around with access and I'm not getting very far.
Thanks

 

[Rick Brandt]

I'm sorry. I don't think I explained my problem well enough. I
don't have a two field table. I know how to run a query and select
for a certain criteria to base the query on. My problem is, I hope,
pretty simple. When I create a report from a query, I select the
fields from the field list and arrange them in a certain order and
style that makes sense. Since I am using this same layout for
multiple states, I would like to be able to save the way I have
designed the layout for the report and use this version for all the
other states. I hope this is clearer. Thanks for your help.

Copy the report then change the RecordSource on the new copy to a different
query. Sounds to me though that one query for all states should be made and a
single report could be filtered for the desired state. Any time you have
multiple tables with the same structure it strongly indicates an improper
design.

 

[Guest]

First of all, I did make one querry for all the states. Secondly, I do not
have multiple tables, I have one table. You still do not seem to understand
what I am asking. All I want to be able to do is copy the design I made for
the report using the field list box. I had to drag each field from the list
box to the report in design view. As this took a while, I was just hoping
that it was possible to save that design and use it for the next time. Thank
you.

 

[Rick Brandt]

First of all, I did make one querry for all the states. Secondly, I
do not have multiple tables, I have one table. You still do not seem
to understand what I am asking. All I want to be able to do is copy
the design I made for the report using the field list box. I had to
drag each field from the list box to the report in design view. As
this took a while, I was just hoping that it was possible to save
that design and use it for the next time. Thank you.

And I said that you can copy the entire report and then change the
RecordSource to a different query. If the new query has the same field
names then the new report should just work. If they are not the same then
you would have to change the ControlSource of each control to match the
appropriate field name of the new query. This would require "some" rework,
but way less than building the report all over again. Most of the time
spent on a report is in laying out the locations of the controls and all of
that work would not have to be repeated.

 

[Guest]

It worked! Thank you very much! Sorry I got rude, but I was under a lot of
pressure to figure this out.