MS Access question

[archcommus]

I have many queries in a database that all pull criteria from a variety of forms. Many of the queries are identical except for being used in conjunction with different forms. So far I've been copying and pasting these queries, renaming them something different, and then changing the appropriate criteria fields to match the right form. Is there any way to avoid this, to just have one query that can pull data from different forms? I was considering a subform that say would let you pick a member and date range, and then that subform could be used in many other forms and the queries could reference the subform, but I don't think this works.

Any ideas? Thanks.

 

[EagleKeeper]

I have used a temp table to prep data that is used by a query.

Query is fixed to pull data from the temp table field.

Forms update the temp table as needed then activate the query.

 

[archcommus]

Excellent idea, thank you!

 

[archcommus]

Can you explain the syntax for this? It doesn't seem I can just do Tables!TableName!Field like I can do Forms!FormName!TextBox.

 

[EagleKeeper]

Concept:

From the form you extract data and store it into a table (ex: labeled Temp_For_Query).
Within the tables are fields for each piece of data.

The "Master" query selects the data from each field that it needs to act as parameters

 

[archcommus]

Thanks, I understand the concept, but how are you implementing this "master" query? I suppose directly in the Criteria line I could put: ALike (SELECT Member FROM Temp_For_Query), but that would make it take a massive amount of time since it'd be doing an extra query for every single record.

Should I make the master query a saved query, and then "include" it like a table in every query that needs it? The only way it seems that would work is if I then did joins WITH the master query, but then I'm not even really using parameters or the Criteria line anymore, plus that wouldn't work with things like Between.

 

[EagleKeeper]

I do not play with Access forms therefore can not help you any futher in this regard.

However, the query into the Temp should become stored in cache and therefore not "executed" as second time unless the data changes.

Good Luck

 

[archcommus]

Thanks for your help. I understand your idea, and don't even really need any form assistance. I'm just still confused about where this master query should go and how the main query should use its results as criteria. I figured this part you could explain since you said you had done this before, but that's okay.

 

[EagleKeeper]

Think of it as joining two tables together.

I was using the method described to allow a client to dynamically create customized reports

 

[archcommus]

Ahh....that's what I thought you might've been doing. Good idea, but that won't work for things like Between statements, since nothing "equals" anything else, it just has to be between two values. In that case it seems the only way to do it would be to put a select statement in the between statement, for example Between (SELECT StartDate FROM Temp_For_Query) AND (SELECT EndDate FROM Temp_For_Query), but that makes the main query take a very long time to complete.

Oh well, we tried. I've taken enough of your time today, thanks for the help!

 

[snapper316]

How about making a global date range form and have command buttons show up depending on if the criteria form is loaded?


i.e. every form you have now w/ startdate and endDate take those boxes off, add a command button that only says "reports" this will open the new global date range form.......depending on what forms are loaded (IsLoaded property) command buttons are visible/invisible

each command button would open the proper report for that form



Or another option

Put your criteria on the Main Form and have all the other forms that use those criteria as subforms in a tab control

 

[archcommus]

Thanks for the suggestions. Both would work, however I ended up going with a slightly different approach to avoid any significant changes to my already existing forms. I left all my forms unchanged, except for when you click the button to run the report, it first copies all the values over to another form that is hidden, and all queries are set to grab their parameters from this form. Works very well.