Use 1 report for multiple queries?

[Rosier75]

Looking for some solutions to my problem.



I have several forms in my access database with multiple text fields for users to enter data and search a common table. I have separate queries for each of the possible outcomes of criteria entered for each form and each query has a corresponding report. This has resulted in 100+ queries and 100+ reports and has made my database excessively large.

My reports are actually grouped by form and I have 5 search forms (Ex. Form1 = 25 queries/25 forms, Form2 = 31 queries/31 forms, etc). I wanted to create 1 form template for each of the corresponding groups which would reduce my forms from 100+ to 5 but cannot figure out how to accomplish this. I am using Access 2007 and am not well versed in VBA or SQL statements but assume this is the way to go.

Any suggestions would be greatly appreciated.

[alansidman]

Are your queries similar and only change based upon the criteria within certain fields? If this is the case then you could create a search form that allows you to put the variable criteria in an unbound text box and make that text box the criteria for your query. If this is not the case, then you will have to resort to using VBA. In that case you would take your SQL statement for each of your queries and put it in a VBA command. A little bit of labor but not really all that difficult.

[Rosier75]

First, Thanks for the quick reply.

All of my queries are similar based on the specific group. They have the same output fields and only change based on the combination of search criteria used. Each user input field is an unbound text box.

Example:
Form 1 = 4 input fields
Query 1 = Text1 only
Query 2 = Text1 & Text2
Query 3 = Text1 & Text3
etc.

I have set the query criteria for each individual query:
Field A: Like [Forms]![Search Screen 1]![Text1] & "*"
Field B: Like "*" & [Forms]![Search Screen 2]![Text3] & "*"
etc.

So I think the answer to your first question is Yes and it seems I've already done the unbound text box as query criteria.

I've also tried to combine the various search parameters in one query but that never worked out as expected. I'm currently working on creating a 'make table' query that stores the results of each of the queries (overwriting the table since the data fields are the same) and using the resulting table create one datasheet view and one report view for each of the groups. That's my current work around but I think it has to be an easier way.

[Rosier75]

ok - was able to reduce the size of my database by 72.5% (from 35.7MB to 9.8MB) as follows:

1. I created tables for each one of my corresponding forms as a holding spot for my data when queries are run.
2. I converted all queries from 'select' to 'append' (appending to the approprate holding table).
3. I created 2 new queries for each form type: 1 to delete all data in the holding table and another as a select query to show my data.
4. Using 1 report for each group (since my data output and format is uniform within each group), changed the data source to the corresponding holding table.
5. Delete all extra reports
6. Update Macros to add newly created tables (delete and select) in the appropropriate order.

Will go through and convert all macros to VBA. Also, as I am typing this, I don't think I actually need the 'select' queries I created in step 3 so deleting those will free up additional space.

I'm sure it is a better more efficient way to do this but this works for me.
Thanks for the help.

[alansidman]

One more thing. Since you are making lots of changes, be sure to run a compact and repair. It is important to do that during development as Access does not always give back space when we delete things. A C&R will solve that.

Alan