Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2014
    Posts
    20

    Unanswered: Help with query with two calculations

    I have an Access 2007 database table which lists the attendance for each class and on each date for a school. Its main table has ~22k records (53 classes x 400 days of attendance). I want to calculate the % change in attendance over the past month, quarter and year for each class in the school whenever I run this query. I eventually want to chart this using the bubble chart in the Access charting function (quarter change by year change, with size based on another value from a separate table [test scores]) as a report.

    I have tried several ways to make this happen:

    1) Separate queries for each value
    - Query to find the latest attendance
    - Query to find the month-ago attendance
    - Query which calculates the % change in attendance over one month
    - Same for quarter change
    - Same for year change
    - Query to combine the quarter and year change into a single query results in a query which takes >30 minutes to complete.
    - Result: although this method is logical, it doesn’t seem to be practical; I must be ordering these queries such that this is very inefficient. (I have indexed the dates, attendance, classes.)

    2) Rank all the dates and then separate queries
    - Query which ranks the dates of each class’s attendance for the past year (the [Last Years Attendance RANKED] query as reference below) which runs quickly (~10 seconds)
    - Query to read only the current attendances for each class
    - Separate queries to read the month-ago, quarter-ago and year-ago attendances for each class
    - I created a report which charts the year % change in attendance by class and it works beautifully (opens in ~15 seconds). Same with quarter-ago and month-ago.
    - However, when I try a query combining two of these values (say quarter-ago % change and year-ago % change) the query is very slow (>30 minutes to open. Same with a chart taking in these values.
    - I’ve tried joining the queries, I’ve tried calling the fields from the two separate queries – nothing seems to help. Have I overly complicated things with too many queries?


    3) Rank all the dates and pull into a separate table
    - Query which ranks the dates of each class’s attendance for the past year (the [Last Years Attendance RANKED] query as reference below) which runs quickly (~10 seconds)
    - Query to read only the current, month-ago, quarter-ago and year-ago attendances for each class
    - I created a pivot table in this query where the “class” runs down the rows, the “ranking” runs across the columns, and the pivot table body is filled with the attendance numbers.
    - I now need to calculate the current date by the month-ago, quarter-ago etc. attendance but these values are all in the same field

    - QUESTION: how can I pull the current attendance out to use in calculation?

    o I tried adding an IIF statement to create an expression called CurrentAttendance which is only when ranking = 2 (the most recent date; it works so I didn’t try to fix this). However, CurrentAttendance then had a date or “ranking” element so that when “ranking” =2 the CurrentAttendance was the attendance, but CurrentAttendance is null for anything else (I want it to be the attendance when ranking=2 – i.e. CurrentAttendance would be the same number for all rankings/dates even though this would produce duplicate cells for this field). The SQL is below:

    SELECT [Last Years Attendance RANKED].*, iif(ranking="2",QueryAttendance) AS CurrentAttendance
    FROM [Last Years Attendance RANKED]
    WHERE (ranking=92) or (ranking=2) or (ranking=22) or (ranking=252);


    o I tried adding another expression as CurrentAttendance – (SELECT QueryAttendance FROM [Last Years Attendance RANKED] WHERE ranking=2) AS CurrentAttendance. However this gives me the error “at most one record can be returned by this subquery”.

    o I tried adding another expression as CurrentAttendance from a query which returns the latest attendance for each class – (SELECT QueryAttendance FROM [Show Latest Attendance]) AS CurrentAttendance. However this, too, gives me the error “at most one record can be returned by this subquery”.




    The query that I would like to work is:

    SELECT [Last Years Attendance RANKED].*, IIF(ranking="2",QueryAttendance,(SELECT QueryAttendance from[Last Years Attendance RANKED] where (ranking=2))) AS CurrentAttendance
    FROM [Last Years Attendance RANKED]
    WHERE (ranking=92) or (ranking=2) or (ranking=22) or (ranking=252);



    The database is schooldata.mdb
    The table is ClassData
    The Class is in ClassData.Class
    The Date is in ClassData.Date
    The Attendance is in ClassData.Attendance
    (Each Class in ClassData has many other columns beyond Attendance and Date such as ClassData.Absences.)
    For any date and class there will be only one attendance number.

    An example is below:

    Date Class Attendance
    1/7/2014 Albertson 22
    1/7/2014 Nomura 26
    1/7/2014 Reynolds 25
    1/7/2014 Maplebaum 32
    1/8/2014 Albertson 25
    1/8/2014 Nomura 22
    1/8/2014 Reynolds 33
    1/8/2014 Maplebaum 23
    1/9/2014 Albertson 30
    1/9/2014 Nomura 23
    1/9/2014 Reynolds 27
    1/9/2014 Maplebaum 28
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25

    As a secondary consideration (and significantly farther down the list), I'd like this user defined function (and whatever code -- VBA or other) to be fast/efficient because eventually this table will have tens of thousands of entries as I expand to additional years and schools.

    Thank you for your help on this.

  2. #2
    Join Date
    Jan 2014
    Posts
    20
    Maybe I can ask one of my questions in a simpler way: how can I get this SQL code to work?

    -----------
    SELECT
    [Last Years Attendance RANKED].Class,
    (SELECT [Last Years Attendance RANKED].Attendance FROM [Last Years Attendance RANKED] WHERE ranking=2) AS CurrentAttendance,
    (SELECT [Last Years Attendance RANKED].Attendance FROM [Last Years Attendance RANKED] WHERE ranking=22) AS MonthAgoAttendance,
    (SELECT [Last Years Attendance RANKED].Attendance FROM [Last Years Attendance RANKED] WHERE ranking=252) AS YearAgoAttendance

    FROM
    [Last Years Attendance RANKED];

    ---------------

    I wanted to try it this way so I can then define several other expressions such as CurrentAttendance/MonthAgoAttendance, etc. within the same query.

    I must be doing something wrong with the above SQL code because it returns an error "At most one record can be returned by this subquery". I assume I've made a syntax error? Or is what I am trying to do not allowed?

    Any guidance is greatly appreciated.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd guess its the same query with a changing GROUP BY clause
    if youhave lots and lots of data then it maymake sense to archive off into a summary table the monthly attendance values by the criteria you are suing to GROUP on
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2014
    Posts
    20
    Thanks for your response.

    With regards to archiving, the issue we have is that we want the ability to run these queries every day: tomorrow's MonthAgo and YearAgo dates will march forward by one day as well. So unless I wrote code which preselected these records in advance (and changed them every day), we couldn't really archive the past dates until they reached one-year or older (this we could do).

    Further, I wrote a query which preselected a few records we will require -- CurrentAttendance, WeekAgo, MonthAgo, QuarterAgo and YearAgo. However, they are in the query referenced as class and date, not "AS CurrentAttendance" as an expression I can call. This is what I can't figure out how to do: take the information I've already selected/queried and give it a specific field name. Putting it another way: I want to take 2-dimensional data (attendance for every class by date) and make it into 1-dimensional data (CurrentAttendance for every class, just for ranking=2) as well as a few other 1-dimensional pieces of data from the same table/query (MonthAgoAttendance for every class, YearAgoAttendance for every class, etc.). I am fixated on getting these all in one query referencing [Last Years Attendance RANKED] because every time I add a few query steps the processing time increases geometrically.

    With regards to your comment "I'd guess its the same query with a changing GROUP BY clause", I am sure you understand how to design this correctly (versus me as a novice). You are correct: the SQL looks pretty much identical for the separate queries for CurrentAttendance, MonthAgo, etc. However, I don't understand how I would proceed this way: won't GROUP BY still produce all records but sort them in a specific way?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I want to calculate the % change in attendance over the past month, quarter and year for each class in the school whenever I run this query.
    isnt the same as
    tomorrow's MonthAgo and YearAgo dates will march forward by one day as well.
    but if you are looking back at specific DAYS then you could use a subselect /subquery to pull out the day(s) going back in time
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2014
    Posts
    20
    Thanks for your reply.

    A subquery sounds great, particularly one which would allow me to define a few fields such as CurrentAttendance, etc. I saw your response immediately yesterday and spent several hours trying a range of different subquery solutions. However, I am having significant issues getting any to work. For instance, why doesn’t the below query work?

    -------------------
    SELECT
    [Last Years Attendance RANKED].Class,
    (SELECT [Last Years Attendance RANKED].Attendance FROM [Last Years Attendance RANKED] WHERE ranking=2) AS CurrentAttendance,
    (SELECT [Last Years Attendance RANKED].Attendance FROM [Last Years Attendance RANKED] WHERE ranking=22) AS MonthAgoAttendance,
    (SELECT [Last Years Attendance RANKED].Attendance FROM [Last Years Attendance RANKED] WHERE ranking=252) AS YearAgoAttendance

    FROM
    [Last Years Attendance RANKED];

    -----------------------

    The above query/subquery seems to make sense to me: it calls the classes, it then finds the CurrentAttendance by class as a field (without the 2nd dimension of dates); same with MonthAgoAttendance, etc. But it returns the error “at most one record can be returned by this subquery”. Is this a bad way to go with the subquery?

  7. #7
    Join Date
    Jan 2014
    Posts
    20
    Any help is appreciated. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •