Run Multiple Reports Through Form

[STEVENCV]

Hi,

I have an access database that is going to require around 30 different reports running off it on a regular basis. A lot of the reports are similar with slight changes.

Is it possible to create a form where a user inputs all the filters they wish to apply and then it runs a report? I need help creating the queries also.

For example, here are 3 reports:

1) The query looks for the 'Start Date on Service' field, and request a start date and end date, displaying records within those dates. (Note, I have created this query and it works)
2) Same as above, but also only shows those within the dates that also have the drop down field 'Client Type' option of 'Self Pay'. (How do I add this so it only displays those records?)
3) As above, but change 'Self Pay' to 'Response Only'

There are a few more like that, and then some other similar ones. If I know how to do multiple filters (i.e. date range AND drop down box option) I can transfer this across multiple queries.

I am able to create reports using my queries as a record source.

However, listing 30 or so reports to be ran will be difficult to browse through. Is there a way of creating a form whereby the user selects which 'type' of report i.e. ("Start Date Report", or "End Date Report") and it then allows them to either 1) show all records within the dates and 2) choose a further criteria based on a drop down list (each report type would be filtered on a different drop down list).

In total, I have 4 report 'types' each with between 4-8 different variations of options within the drop down boxes.

If I have not explained this very well, please don't hesitate to ask for clarification.

 

[pbaldy]

I'm on an iPad right now so it's hard to type code, but you could use this technique

http://www.baldyweb.com/wherecondition.htm

But instead of the simple filter you'd build a string conditional on the user selections.

 

[STEVENCV]

Thank you very much for your help.

However, I have just had a meeting with the boss, and this has all changed now (sigh).

What they require now is ALL the reports running at once, taking a count of the number of records each report outputs, and then exporting those record counts into an excel template.

I am sorry to have wasted your time with the above query, but I was unaware that this would be changing.

I assume this change would make things simpler though?

 

[STEVENCV]

Ah, I was hoping it would be simple.

I have uploaded a sample copy of my database with my queries. If it's ok with you, please can take a look and give me an idea on just how much work it would be to count the records for each report?

There are further queries to be added, but I am working on them. You will notice I have created 1 report, which takes it's source from one of the queries. This was me testing how I could do a record count on a report. If you know of a better way of counting records I would be grateful to hear it.

All of the reports are ran on date ranges (between Start Date and End date). However, the field that is looked up for those dates differs depending on the report. Each time the reports are ran, those dates will be the same though.

For example, if we want figures for May 2012, we have queries that search for records based on 'Start Date On Service' (plus other criteria), some that search for 'End Date of Service' and some that search for 'Review Completed Date'.

Is there a way whereby you can enter this date once, and it will run all of those queries?