Results 1 to 5 of 5
  1. #1
    chriskeeganhw is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    3

    Using a dlookup value as a query criteria but changing the query's behaviour dependent on the value

    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

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    anything is possible, but your description, although comprehensive leaves many questions. Since you problem is with coding, it would be more helpful to

    a) provide some sample data - all relevant tables
    b) provide the SQL code you are using
    c) show the current result the above code is producing (guess that is per your post) based on your sample data
    d) show the required result based on your sample data

  3. #3
    chriskeeganhw is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    3

    Database and further detailed description attached

    Quote Originally Posted by Ajax View Post
    anything is possible, but your description, although comprehensive leaves many questions. Since you problem is with coding, it would be more helpful to

    a) provide some sample data - all relevant tables
    b) provide the SQL code you are using
    c) show the current result the above code is producing (guess that is per your post) based on your sample data
    d) show the required result based on your sample data

    Hi Ajax.

    I have created a Word document and attached it which describes what I am trying to achieve and also the database to which the Word document refers

    Tried uploading them but keep getting a "failed upload" error

    In case the files have not been uploaded, I have placed them on my Dropbox, see the link below

    https://www.dropbox.com/sh/yfu7wzt45...JEeLoKTRa?dl=0

    Thanks

  4. #4
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    that is a lot to take in. had a quick look and those queries I tried to open have a missing field which you are prompted for. I can't see how you can just do this in SQL, you need a form based on your tblDataPack with some vba code to dynamically build the criteria.

    Suggest form would be a continuous form with tblDataPack as its recordsource

    In the form footer put a subform using DLookup_for_Criteria as its recordsource - do not set linkchild/master properties

    using the on current event on the main form or a button have some code along the following lines

    Code:
    dim ctrl as control
    dim filterstr as string
    filterstr=""
    for each ctrl in me.controls
        if ctrl.controltype=actextbox
           if not isnull(ctrl) then 'there is a value to filter
               if isnumeric(ctrl) then
                   filterstr=filterstr & " AND " & ctrl.name & " = " & ctrl   
               else
                   filterstr=filterstr & " AND " & ctrl.name & " = '" & ctrl & "'"
               end if
            end if
        end if
    next ctrl
    if filterstr<>"" then
        subformname.form.filter=filterstr
        subformname.form.filteron=true
    else
        subformname.form.filteron=false
    end if
    the above is pseudocode - you'll need to provide the correct names and may need to do things a bit differently depending on how your kpi's are calculated

  5. #5
    chriskeeganhw is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    3
    Quote Originally Posted by Ajax View Post
    that is a lot to take in. had a quick look and those queries I tried to open have a missing field which you are prompted for. I can't see how you can just do this in SQL, you need a form based on your tblDataPack with some vba code to dynamically build the criteria.

    Suggest form would be a continuous form with tblDataPack as its recordsource

    In the form footer put a subform using DLookup_for_Criteria as its recordsource - do not set linkchild/master properties

    using the on current event on the main form or a button have some code along the following lines

    Code:
    dim ctrl as control
    dim filterstr as string
    filterstr=""
    for each ctrl in me.controls
        if ctrl.controltype=actextbox
           if not isnull(ctrl) then 'there is a value to filter
               if isnumeric(ctrl) then
                   filterstr=filterstr & " AND " & ctrl.name & " = " & ctrl   
               else
                   filterstr=filterstr & " AND " & ctrl.name & " = '" & ctrl & "'"
               end if
            end if
        end if
    next ctrl
    if filterstr<>"" then
        subformname.form.filter=filterstr
        subformname.form.filteron=true
    else
        subformname.form.filteron=false
    end if
    the above is pseudocode - you'll need to provide the correct names and may need to do things a bit differently depending on how your kpi's are calculated


    Hi Ajax

    Many thanks for this. I shall try it out and let you know

    Best regards

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 07-09-2015, 01:39 AM
  2. Replies: 16
    Last Post: 04-28-2015, 03:57 PM
  3. Replies: 2
    Last Post: 12-08-2014, 08:55 AM
  4. Replies: 4
    Last Post: 06-30-2014, 02:53 PM
  5. Changing Query Criteria from Form
    By dvgef2 in forum Queries
    Replies: 4
    Last Post: 06-24-2013, 12:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums