windowing functions ms access

Question

I am working on a class scheduling database in MS Access. There are a variety of classes, each of which is taught multiple times, sometimes multiple times in a day, but not necessarily every day. Each course has a unique set of software and data that is stored on a laptop. there is a set of laptops for each course with that software loaded.

For any given training day I need to assign a range of laptop IDs to the right classes in different rooms, depending on how many people will be taking that class in that room, so that the instructors know which laptops to take to the room with them to teach the class that day.

For example, I have the raw data:

Date   Room   ClassName      HeadCount
----   ----   ---------      ---------
11/30   101   Intro            10
11/30   102   Intro            15
11/30   103   Course 2          5
12/1    101   Intro            10
12/1    102   Course 2         15
12/1    103   Course 3         10

I also know the following about the laptops:

ClassName    LaptopID
---------   ---------
Intro       LT.Intro_1
Intro        ...
Intro       LT.Intro_30
Course 2    LT.Course 2_1
Course 2      ...
Course 2    LT.Course 2_30
Course 3    LT.Course 3_1   
Course 3    ...
Course 3    LT.Course 3_30

Based on the above two tables, I would want to output:

Date   Room   ClassName     HeadCount  First Laptop   Last Laptop
----   ----   ---------     ---------  ------------   -----------
11/30   101   Intro           10       LT.Intro_1     LT.Intro_10
11/30   102   Intro           15       LT.Intro_11    LT.Intro_25
11/30   103   Course 2         5       LT.Course 2_1  LT.Course 2_5
12/1    101   Intro           10       LT.Intro_1     LT.Intro_10
12/1    102   Course 2        15       LT.Course 2_1  LT.Course 2_15
12/1    103   Course 3        10       LT.Course 3_1  LT.Course 3_10

I know this is a windowing function, but MS Access doesn't have lead or lag. Is there a workaround?

Switch to another database. Although we are not supposed to make software recommendations, I can point out that SQL Server Express is made by the same vendor, is free, and supports window functions. — Gordon Linoff, Nov 16 '17 at 18:12
Would you be able to create another column in your second table where the format of LaptopID is "###_TEXT', so like '001_LT.Intro', '030_LT.Intro', etc.? If so (and you added a HeadCount column to the second table as well) you could then use MAX and MIN with this new column and group by ClassName and HeadCount. — Pops, Nov 16 '17 at 18:46
are they your actual table definitions or query results? It is possible with access but not sure abour performance. — Krish, Nov 17 '17 at 10:36

score 2 · Answer 1 · answered Nov 17 '17 at 11:37

You might want to change your table definitions for better performance. I have recreated two tables as you've mentioned.

You know your laptop ids are in sequence and you know the headcount per class. In order to follow a lead, you must know the last headcount. which would be toal attendees on the same date, for the same class, before current class/event.

x = sum(headCount) where id < currentID & classname = currentClassname & date = currentDate. (Current means currentRow.)

Now you know total laptops used before the current row and the headCount for current row. The First laptop would be

f = min(laptopid) where laptopid > x (x being totaLaptopUsedBefore this Row)

for the Last laptop, you must also add the current headcount.

l = min(laptopid) where laptopid >= currentHeadCount + x

Note f checks laptopid is greater and L checks >=.

Here is a working demo which you can improve on:

Table1: tbl_ClassEvents

+----+------------+------+-----------+-----------+
| ID |    date    | Room | ClassName | HeadCount |
+----+------------+------+-----------+-----------+
|  1 | 30/11/2017 |  101 | Intro     |        10 |
|  2 | 30/11/2017 |  102 | intro     |        15 |
|  3 | 30/11/2017 |  103 | Course 2  |         5 |
|  4 | 01/12/2017 |  101 | Intro     |        10 |
|  5 | 01/12/2017 |  102 | Course 2  |        15 |
|  6 | 01/12/2017 |  103 | Course 3  |        10 |
|  7 | 17/11/2017 |  101 | Intro     |        16 |
+----+------------+------+-----------+-----------+

Table2: Tbl_ClassVsLaptop
+----+-----------+----------------+
| Id | ClassName |    LaptopId    |
+----+-----------+----------------+
|  1 | Intro     | LT.Intro_1     |
|  2 | Intro     | LT.Intro_2     |
|  3 | Intro     | LT.Intro_3     |
|  4 | Intro     | LT.Intro_4     |
|  5 | Intro     | LT.Intro_5     |
|  6 | Intro     | LT.Intro_6     |
|  7 | Intro     | LT.Intro_7     |
|  8 | Intro     | LT.Intro_8     |
|  9 | Intro     | LT.Intro_9     |
| 10 | Intro     | LT.Intro_10    |
| 11 | Intro     | LT.Intro_11    |
| 12 | Intro     | LT.Intro_12    |
| 13 | Intro     | LT.Intro_13    |
| 14 | Intro     | LT.Intro_14    |
| 15 | Intro     | LT.Intro_15    |
| 16 | Intro     | LT.Intro_16    |
| 17 | Intro     | LT.Intro_17    |
| 18 | Intro     | LT.Intro_18    |
| 19 | Intro     | LT.Intro_19    |
| 20 | Intro     | LT.Intro_20    |
| 21 | Intro     | LT.Intro_21    |
| 22 | Intro     | LT.Intro_22    |
| 23 | Intro     | LT.Intro_23    |
| 24 | Intro     | LT.Intro_24    |
| 25 | Intro     | LT.Intro_25    |
| 26 | Intro     | LT.Intro_26    |
| 27 | Intro     | LT.Intro_27    |
| 28 | Intro     | LT.Intro_28    |
| 29 | Intro     | LT.Intro_29    |
| 30 | Intro     | LT.Intro_30    |
| 31 | Course 2  | LT.Course 2_1  |
| 32 | Course 2  | LT.Course 2_2  |
| 33 | Course 2  | LT.Course 2_3  |
| 34 | Course 2  | LT.Course 2_4  |
| 35 | Course 2  | LT.Course 2_5  |
| 36 | Course 2  | LT.Course 2_6  |
| 37 | Course 2  | LT.Course 2_7  |
| 38 | Course 2  | LT.Course 2_8  |
| 39 | Course 2  | LT.Course 2_9  |
| 40 | Course 2  | LT.Course 2_10 |
| 41 | Course 2  | LT.Course 2_11 |
| 42 | Course 2  | LT.Course 2_12 |
| 43 | Course 2  | LT.Course 2_13 |
| 44 | Course 2  | LT.Course 2_14 |
| 45 | Course 2  | LT.Course 2_15 |
| 46 | Course 2  | LT.Course 2_16 |
| 47 | Course 2  | LT.Course 2_17 |
| 48 | Course 2  | LT.Course 2_18 |
| 49 | Course 2  | LT.Course 2_19 |
| 50 | Course 2  | LT.Course 2_20 |
| 51 | Course 2  | LT.Course 2_21 |
| 52 | Course 2  | LT.Course 2_22 |
| 53 | Course 2  | LT.Course 2_23 |
| 54 | Course 2  | LT.Course 2_24 |
| 55 | Course 2  | LT.Course 2_25 |
| 56 | Course 2  | LT.Course 2_26 |
| 57 | Course 2  | LT.Course 2_27 |
| 58 | Course 2  | LT.Course 2_28 |
| 59 | Course 2  | LT.Course 2_29 |
| 60 | Course 2  | LT.Course 2_30 |
| 61 | Course 3  | LT.Course 3_1  |
| 62 | Course 3  | LT.Course 3_2  |
| 63 | Course 3  | LT.Course 3_3  |
| 64 | Course 3  | LT.Course 3_4  |
| 65 | Course 3  | LT.Course 3_5  |
| 66 | Course 3  | LT.Course 3_6  |
| 67 | Course 3  | LT.Course 3_7  |
| 68 | Course 3  | LT.Course 3_8  |
| 69 | Course 3  | LT.Course 3_9  |
| 70 | Course 3  | LT.Course 3_10 |
| 71 | Course 3  | LT.Course 3_11 |
| 72 | Course 3  | LT.Course 3_12 |
| 73 | Course 3  | LT.Course 3_13 |
| 74 | Course 3  | LT.Course 3_14 |
| 75 | Course 3  | LT.Course 3_15 |
| 76 | Course 3  | LT.Course 3_16 |
| 77 | Course 3  | LT.Course 3_17 |
| 78 | Course 3  | LT.Course 3_18 |
| 79 | Course 3  | LT.Course 3_19 |
| 80 | Course 3  | LT.Course 3_20 |
| 81 | Course 3  | LT.Course 3_21 |
| 82 | Course 3  | LT.Course 3_22 |
| 83 | Course 3  | LT.Course 3_23 |
| 84 | Course 3  | LT.Course 3_24 |
| 85 | Course 3  | LT.Course 3_25 |
| 86 | Course 3  | LT.Course 3_26 |
| 87 | Course 3  | LT.Course 3_27 |
| 88 | Course 3  | LT.Course 3_28 |
| 89 | Course 3  | LT.Course 3_29 |
| 90 | Course 3  | LT.Course 3_30 |
+----+-----------+----------------+

Here is the query:

SELECT tbl_classEvents.ID
    ,tbl_classEvents.DATE
    ,tbl_classEvents.Room
    ,tbl_classEvents.ClassName
    ,tbl_classEvents.HeadCount
    ,(
        SELECT min(laptopId)
        FROM tbl_ClassVsLaptop T1
        WHERE T1.ClassName = tbl_ClassEvents.ClassNAme
            AND Mid([T1.LaptopID], InStrRev([T1.LaptopID], "_") + 1, 3) > (
                + Nz((
                        SELECT sum(headCount)
                        FROM tbl_classEvents T2
                        WHERE T2.ID < Tbl_ClassEvents.ID
                            AND T2.[DATE] = [Tbl_ClassEvents].[DATE]
                            AND T2.[ClassName] = [Tbl_ClassEvents].[ClassName]
                        ), 0)
                )
        ) AS FirstLaptop
    ,(
        SELECT min(laptopId)
        FROM tbl_ClassVsLaptop T1
        WHERE T1.ClassName = tbl_ClassEvents.ClassNAme
            AND Mid([T1.LaptopID], InStrRev([T1.LaptopID], "_") + 1, 3) >= (
                + [tbl_classEvents].[HeadCount] + Nz((
                        SELECT sum(headCount)
                        FROM tbl_classEvents T2
                        WHERE T2.ID < Tbl_ClassEvents.ID
                            AND T2.[DATE] = [Tbl_ClassEvents].[DATE]
                            AND T2.[ClassName] = [Tbl_ClassEvents].[ClassName]
                        ), 0)
                )
        ) AS LastLaptop
FROM tbl_classEvents
ORDER BY tbl_classEvents.DATE
    ,tbl_classEvents.Room
    ,tbl_classEvents.ClassNAme;

And the output:

+----+------------+------+-----------+-----------+---------------+----------------+
| ID |    DATE    | Room | ClassName | HeadCount |  FirstLaptop  |   LastLaptop   |
+----+------------+------+-----------+-----------+---------------+----------------+
|  7 | 17/11/2017 |  101 | Intro     |        16 | LT.Intro_1    | LT.Intro_16    |
|  1 | 30/11/2017 |  101 | Intro     |        10 | LT.Intro_1    | LT.Intro_10    |
|  2 | 30/11/2017 |  102 | intro     |        15 | LT.Intro_11   | LT.Intro_25    |
|  3 | 30/11/2017 |  103 | Course 2  |         5 | LT.Course 2_1 | LT.Course 2_5  |
|  4 | 01/12/2017 |  101 | Intro     |        10 | LT.Intro_1    | LT.Intro_10    |
|  5 | 01/12/2017 |  102 | Course 2  |        15 | LT.Course 2_1 | LT.Course 2_15 |
|  6 | 01/12/2017 |  103 | Course 3  |        10 | LT.Course 3_1 | LT.Course 3_10 |
+----+------------+------+-----------+-----------+---------------+----------------+

windowing functions ms access

1 Answers1