Multiple Versions of Same Report

[Amy E. Baggott]

I have a report that takes a list of exhibitors in the current show and a
list of the exhibitors in the last show and compares the square footage
purchased between the two shows (how much each company had in each show).
However, when my bosses need these comparisons, they frequently want a
filtered list such as only companies who are in this year, but not last year;
companies who were in last year, but have not yet re-upped for this year;
companies that were in both years and increased space this year; etc.) Is
there any way in Access 2003 to have one report/query combo filtered in
different ways at runtime, or do I have to actually have half a dozen or
eight different versions of the same report or query?

 

[M Skabialka]

I was working with people who needed to get the same report format but with
multiple variations in the data. I created a form with list boxes of each
of the fields they were likely to choose from (about a dozen) and let them
choose one or more items in each list box. They clicked a Create Query
button, then a Run Report button. There was also a Clear selections button.
The create query button looked through each of the list boxes and created
the report query in a hidden text box on the form. The report then used
this as the record source. With multiple items per list box they have
literally thousands of variation in the reports - tailored exactly to their
needs.
The list box data contains distinct info from the fields in the table, so
all choices are represented. I even allowed blank or null as an option if
it was in the table.
One report, a dynamic query - they can't run it enough! Multiple variations
of a report will give you nightmares forever!
Mich

 

[Jeff Boyce]

You can simplify that even more...

Rather than force the user to <Create Query> first, you can do that bit of
dynamic query/SQL building as the first step in the code behind the <Run
Report> command button.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was working with people who needed to get the same report format but with
multiple variations in the data. I created a form with list boxes of each
of the fields they were likely to choose from (about a dozen) and let them
choose one or more items in each list box. They clicked a Create Query
button, then a Run Report button. There was also a Clear selections
button. The create query button looked through each of the list boxes and
created the report query in a hidden text box on the form. The report then
used this as the record source. With multiple items per list box they have
literally thousands of variation in the reports - tailored exactly to their
needs.
The list box data contains distinct info from the fields in the table, so
all choices are represented. I even allowed blank or null as an option if
it was in the table.
One report, a dynamic query - they can't run it enough! Multiple
variations of a report will give you nightmares forever!
Mich

 

[Amy E. Baggott]

The only things that change for this report are the filters, so would I use a
list box so they could select the groups they want and then have that build
the criteria? If I'm building the query on the fly, how do I tie it in to
the report?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

You can simplify that even more...

Rather than force the user to <Create Query> first, you can do that bit of
dynamic query/SQL building as the first step in the code behind the <Run
Report> command button.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[M Skabialka]

The reason I do it in two steps is because I also show on the form the
criteria in easy to read format. So they also see [Year] = 2006 AND
[Location] = 'New York' on the form and on the report. They can review
their choices before running the report.

You can simplify that even more...

Rather than force the user to <Create Query> first, you can do that bit of
dynamic query/SQL building as the first step in the code behind the <Run
Report> command button.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[M Skabialka]

My criteria to build the criteria is only the filter info - it isn't the
record source as I mis-stated earlier. The record source remains static.
e.g. Me!strSQL =
(INDEXCD.Disposition = 'Order') AND ((INDEXCD.PhysicalInventoryCompleted
= -1)) AND (INDEXCD.Location = 'Pierce')

DoCmd.OpenReport stDocName, acViewPreview, , Me!strSQL

 

[Amy E. Baggott]

In my case, I have five basic groups:
Exhibitors in the current show that weren't in the last show
Exhibitors in both shows who increased space for the current show
Exhibitors in both shows who kept the same space for the current show
Exhibitors in both shows who decreased space for the current show
Exhibitors in the last show who aren't in the current show (yet)

However, I frequently need to filter on combinations of these groups (e.g.,
Exhibitors who either decreased space or have not yet come back in). What is
the best way to handle that? I'm thinking a list box and then run some form
of concatenation of the selected groups with OR in between them, with the
Select All simply not filtering at all.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

The reason I do it in two steps is because I also show on the form the
criteria in easy to read format. So they also see [Year] = 2006 AND
[Location] = 'New York' on the form and on the report. They can review
their choices before running the report.

You can simplify that even more...

Rather than force the user to <Create Query> first, you can do that bit of
dynamic query/SQL building as the first step in the code behind the <Run
Report> command button.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Amy E. Baggott]

It looks like I'm going to have to be able to change the sort order of the
report on demand as well. The original report was constructed to sort
alphabetically, but my boss just came in and asked for it sorted by sq.ft.
I'm assuming I would simply reset the "Order By" property in the report based
on their selection.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

The reason I do it in two steps is because I also show on the form the
criteria in easy to read format. So they also see [Year] = 2006 AND
[Location] = 'New York' on the form and on the report. They can review
their choices before running the report.

You can simplify that even more...

Rather than force the user to <Create Query> first, you can do that bit of
dynamic query/SQL building as the first step in the code behind the <Run
Report> command button.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

Is
there any way in Access 2003 to have one report/query combo filtered in
different ways at runtime, or do I have to actually have half a dozen or
eight different versions of the same report or query?

Microsoft Access Report Printing Criteria Selection Form
http://www.granite.ab.ca/accsrprt.htm

Tony

 

[Tony Toews [MVP]]

It looks like I'm going to have to be able to change the sort order of the
report on demand as well. The original report was constructed to sort
alphabetically, but my boss just came in and asked for it sorted by sq.ft.
I'm assuming I would simply reset the "Order By" property in the report based
on their selection.

Correct.

Tony