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

Jumat, 21 November 2014

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

Jerry,

Your reply seems to suggest issues with your data structures. "adding or subtracting allowed accommodations" should involve only updates to data, never to designs of tables, queries, reports, or forms.

Can you enlighten us about your application and tables?

Duane Hookom MVP
MS Access

Date: Fri, 21 Nov 2014 17:10:15 +0000
Subject: Re: [MS_AccessPros] Do I really need all of these Queries?

Thanks for the suggestions. I appreciate it.

Just to clarify, the situation has to do with accommodations a student could have when they take the state-mandated tests at the end of the school year. For example for the Math test a student might have an accommodation allowing her to use a calculator. Another student might have accommodations for a calculator and ruler. Still another might be allowed to use a ruler, a paper/pencil version and a dictionary, etc.

That's just Math and these are a small subset of possible accommodations (Math has 18 possibilities, Language Arts has 16, Science has 15, Social Studies has 13). Some of the accommodations overlap for all four subjects (there are 10 in common). Some of them are mutually exclusive to each other and cannot be combined. But I hope you can see how it can be complicated to account for every legit possibility. That's why I took the approach of creating Queries for almost all of the possibilities. Each Query is tied into a separate corresponding Report as Duane inferred. Each Report represents a "testing group" meaning those students have the same accommodations and will be tested as a group in the same room, but separate from other testing groups. This is how we decide who is tested together which helps us figure out the logistics of administering the test to an entire grade level on a given day.

It does work, though it is clunky and was a lot of work to create. My problems came when the state kept adding or subtracting allowed accommodations causing me to have to go back an modify the mess I made. New accommodations have been added this year which meant I had to get back into things to make the changes. Looking ahead at the scope of how one change can cause a cascade of changes the "I created a monster" thought popped into my head as I started in on it.

-Since there are lots of possibilities will the Where condition in a DoCmd be efficient AND easy to modify. Plus, will it display only the fields I'd need.

-I'm not sure that asking the user to respond to prompts would work because they'd be there a while responding to all of the possible prompts.

-Storing the SQL in the Record Source isn't something I'm familiar with so I will definitely check out the article.

As I said, I do appreciate the suggestions and it has given me directions to look into. I'm sure this is a fairly simple program compared to "real" programs created by true database admins, but to me and my fairly new and limited Access skills this has been a big--and most fun and engaging challenge.

On Tuesday, November 18, 2014 2:00 PM, "Jerry bearjercares@gmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

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....