Hi
I wonder if anyone can guide/advise me on something I am trying to achieve in Access 2010.
I should start off by saying I am not an experienced VBA or SQL programmer but am able to use Access’s interface to create queries, do updates and so on
I have inherited a database in my role working for a school in the UK.
There are tables that contain some information.
The first, “Master” data, contains info about the student (e.g date of birth, special needs, joining date, demographic information and so on).
The second is “Progress” data and contains things like subjects being studied, grades, marks, class name and so on throughout the academic year.
These already exist, have a variety of queries that pull up certain information from either or both joined tables and are working well.
But …
There is a need to undertake some analysis of what is happening not for each individual but for different groups of students and/or what is happening within a subject (or indeed a combination of these. For example find me all females who are hearing impaired who are meeting their targets.
It turns out that for each of these group analyses, some data comes from the “Master” table and some from the “Progress” table.
Furthermore as data is being entered regularly, the need to re-calculate and distribute all these analyses is important, and the results of the analyses are written to a third table – call it “KPI” table which basically contains the count of students, males and females for each KPI
There are potentially thousands of different combinations of criteria to combine to get the results of who is doing what to produce some KPIs
Using the example above of “find me all females who are hearing impaired who are meeting their targets” is a query that applies 3 criteria to my access query that joins the table and is one KPI
But there are several other KPIs that are closely related to this but do vary a little. For example …
find me all females who are hearing impaired who are meeting their targets and are aged 11
find me all females who are hearing impaired who are meeting their targets and are aged 12
find me all females who are hearing impaired who are meeting their targets and are aged 13
And then more criteria can be added, eg
find me all females who are hearing impaired who are meeting their targets and are aged 11 and whose attendance is more than 90%
And so it goes on – the potential combinations literally turn into thousands as each combination is in effect a KPI in its own right
So I started off by making a select query from the join of the 2 tables that gets the first combination of the different criteria and then use an update query to update the appropriate record in the KPI table.
All of this works brilliantly EXCEPT that it is a maintenance nightmare as I have already got 60 select queries each of which has an associated update query for a given combination. And I have only just started.
So I thought that perhaps a better way to go would be to have the KPI table with all the different criteria as records, create a single “all purpose” query, and then a single “update” query to write the counts of students for each KPI back into the KPI table.
So the KPI table might look like this (only included a small number of criteria for illustrative purposes)
KPI Male Female Above Below On Deaf Boarder Literacy – boys above target y n Y N N N Y Literacy – girls above target N Y Y N N N N Literacy – boys on target y n n N Y N N Literacy – girls on target N Y N N N y N
Here is where I need help or guidance ..
In my general purpose join and select query I plan to use dlookup to read the values from the first KPI and apply those values as the criteria in my query. (I know dlookup is slow but this is not a time critical application and its OK to run it overnight to re-calculate the answers to each KPI)
My problem is that sometimes the value returned by the dlookup in my general purpose query has a value in in the first record, but then later on a different value
So for example for the first KPI, the dlookup value of “deaf” is an “N” but in the last record it is a “Y”
So my general purpose query works for the first record because the criteria in the query is to join and find all records where “Deaf is N”. But then as I work through each record in the KPI table it fails when it hits record 4 with no records being returned (correctly) because it has applied a criteria of “y” and the query looks for “n”
So what I need to do is to look at the value of a criteria (e.g. deaf), if it is “y” then use that criteria in that query field because it is to be included, but if it is some other value do not include that criteria this time round in the query.
Basically can anyone advise me if what I am trying to achieve with this is possible, and if so how I might start to do it. If not possible, does anyone have a way that I can achieve the same result of having a single list of different combinations of criteria that get applied to the select query to get a count of how many students meet that criteria
Many thanks for taking the time to read this
Regards