ACCESS FEED: Re: [MS_AccessPros] Do I really need all of these Queries?

Selasa, 18 November 2014

Re: [MS_AccessPros] Do I really need all of these Queries?

A different Jerry here - just a thought, would it be helpful to prompt the user for keywords on whatever the search item(s) is for - would that possibly cut down on the large number of queries? Jerry in Michigan, USA

On 11/17/2014 7:17 PM, Crystal strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

Hi Jerry,

I generally store the SQL statement in the RecordSource of Reports and Forms to keep housekeeping on the queries easier. It is a little less efficient -- but the advantage is that the SQL is executed when the object is opened so there is not an issue with saved queries possibly delivering old information.

If you are trying to get a handle on where the queries are being used and what they are, this can help you:

Document Query SQL, Form and Report RecordSource
http://www.rogersaccesslibrary.com/forum/document-query-sql-form-and-report-recordsources_topic606.html

Warm Regards,

Crystal

*

(: have an awesome day :)

*

On Monday, November 17, 2014 11:21 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jerry,

It isn't clear how the query/reports are different. If the only difference is the subset of data they display then you have created a monster. There are many different methods for dynamically filtering a report based on a user making selections on a form.

You can reference controls on forms in the criteria of queries.
You can specify a where condition in the DoCmd.OpenReport method (my favorite)
You can use a little DAO code to change the SQL of a saved query (another favorite)

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 17 Nov 2014 18:08:10 +0000
Subject: [MS_AccessPros] Do I really need all of these Queries?

I may have created a monster, but for now it is working. However, I need to make what should be a small addition to it and realize how adding one simple field creates a cascade in that I now have to account for all of the places where this addition will have an impact (meaning Tables, Queries, Reports and some VBA code). The main thing that comes to mind is in the Reports, and the Queries that are the foundation of the Reports. I have a lot of them (Queries and Reports).

The problem is there are many possible combinations that need to be "reported" on so I created a Query/Report for each possibility. Then, all the user has to do is click on the button for the Report they want. What this all brings up is my wondering if I've gone about this in an inefficient way.

As I was creating them it didn't occur to me that it might not be the way to do it. Now that I have to go back to add in the new request I realize that I have a lot of places to look to see if I need to make a change to account for this new request. I have at least 75 Queries and associated Reports in order to account for all of the possibilities. So the question is, is it unusual to have lots of Queries and Reports? If not, then I'm set and that's just the way things have to be done. If it is unusual, then do you have any suggestions on how I go about re-thinking the way I'm doing things? That might be difficult without knowing something about "all of the possibilities" so I'd gladly provide some info if that helps.

Probably not the typical kind of question....