Jerry,
Thanks for the honest reply. You really need to understand relational databases. Crystal's tutorials are a great time investment http://www.accessmvp.com/strive4peace/.
There should minimally be separate tables for Students, Accommodations, and a junction table that links students with accommodations. The format of your Excel files should never determine the structure of your tables. A proper structured application should require little or no maintenance.
Feel free to continue asking questions. That's the best way to learn and you have some experienced developers willing to provide assistance.
Duane Hookom MVP
MS Access
________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 22 Nov 2014 00:09:04 +0000
> Subject: Re: [MS_AccessPros] Do I really need all of these Queries?
>
>
>
>>Your reply seems to suggest issues with your data structures.
>
> I don't think there's any question that if I knew more about setting it
> up in the first place, much of the modification process wouldn't be so
> crazy. The structure is an embarrassing mess in that it is a flat
> file, single table, that shouldn't be, but I was too far along and too
> unsure....
>
> When I say that I need to add or subtract accommodations, I'm referring
> to a need to add or subtract fields in THE table. That's what causes
> the cascade of changes to the Queries and Reports (and Forms). I don't
> have separate tables which is what I'm sure you'd recommend. For about
> a year I've been occasionally looking over a possible relational
> structure I've drawn on the chalkboard in my room. I'm just not sure
> if it makes sense.
>
> Another part of the problem, and reason I started with a single table
> is due to where I'm getting the list of student testing
> accommodations. They are coming to me in an Excel file. To match
> things up with Excel I basically recreated a similar structure so when
> I import the data it will match up making things easy. I wasn't sure
> how to create a more logical table structure and still be able to
> import the info from Excel and have it go to the correct place/tables.
>
> I've been going round and round wondering if I should create a table
> for all accommodations, regardless of subject, or separate tables for
> accommodations based on the subject. Adding to my confusion is the
> fact that as I mentioned before, 10 accommodations are the same across
> all subjects, while the rest are either unique or apply to only some of
> the four subjects. I didn't know what to do with these "extra"
> accommodations.
>
> I find the relational table thing to be understandable in theory. I'm
> just confused by how to do it in reality. Then I get stuck on how to
> populate the tables.
>
> What I have (in the one table) is:
> -Basic student information (only of the kind that relates to
> testing--Name, ID, Grade, Teacher, ESOL level, Special Ed. status)
> -Test names for each grade (test names are unique, but fall into one of
> the four subject areas, not all grades have a test for all four
> subjects)
> -A list of all possible accommodations as student could receive
> depending on the test subject.
> -A list of specific accommodations each student will be allowed to use
> on the test(s) she/he must take.
> -A list that gives me the source of or cause for the accommodation
> (either ESOL or Special Ed.)
>
> In the end I'd say I'm not a beginner, but I know just enough to often
> get myself into deep trouble. As a teacher this isn't what I do during
> my day. However, it is the kind of thing I attempt because I
> personally enjoy the challenge and learning new things, plus this type
> of program is something that we really need around here because the
> prior method was handwritten paper and pencil, which makes my Access
> mess seem like a golden ticket to Wonkaville. As bad as the structure
> of this is it has made a significant difference in how accurate our
> testing process has been. We're under pretty heavy pressure to make
> sure we comply with all of the state and federal laws related to
> student testing accommodations (yep, there are state and federal laws
> about this). I also share everything I create with other schools in my
> district that are interested in using them. They have the same
> incentive we do, but no one at their school willing to attempt creating
> something that will make the entire process more reliable.
>
> Regardless, I always enjoy reading the postings in the group (though
> they constantly remind of my novice status) and definitely appreciate
> the help I've received in the past. You all have gotten me past many
> roadblocks. I think that the ease with which you are able to offer
> suggestions due to your extensive knowledge and familiarity with Access
> makes it hard for you to know how much us lesser beings do appreciate
> your assistance. I'm sure I speak for many when I say trust me, we
> don't take it for granted!
>
>
>
> On Friday, November 21, 2014 1:58 PM, "Duane Hookom
> duanehookom@hotmail.com [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com> wrote:
>
>
>
> 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<mailto: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<mailto:duanehookom@hotmail.com>
> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com><mailto: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<mailto:MS_Access_Professionals@yahoogroups.com>
> From:
> MS_Access_Professionals@yahoogroups.com<mailto: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....
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
------------------------------------
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
Tidak ada komentar:
Posting Komentar