Drawing down budget

Question

I have table for Users that contains a budget for training

ID | UserID | FName | SName | Dept | Budget
 1 |   1    | John  | Smith |  CS  |  1000
 2 |   2    | Ian   | Caine |  CS  |  2500
 3 |   3    | Jane  | Kelly |  ED  |  1000
 4 |   1    | John  | Smith |  EQ  |  1000
 5 |   2    | Ian   | Caine |  EQ  |  2500
 6 |   3    | Jane  | Kelly |  CS  |  1000

I then have another table Courses that stores the course they have taken;

ID | UserID | Course | Date    | Dept |Cost  
1  |   1    |  CS01  | 1/4/18  | CS   | 100
2  |   2    |  CS01  | 1/4/18  | CS   | 100
3  |   1    |  CS02  | 10/4/18 | CS   | 75
4  |   2    |  CS02  | 10/4/18 | CS   | 75
5  |   1    |  CS01  | 1/4/18  | EQ   | 100

I want to create a form with the the User information at the top and a subform with the Courses at the bottom, something like

UserID | FName | SName | Dept | Budget
  1    | John  | Smith |  CS  |  1000

subfrm    
Course | Date    | Cost | Balance Remaining  
 CS01  | 1/4/18  | 100  | 900
 CS02  | 10/4/18 | 75   | 825

[UPDATE] I am stuck with the underlying query for the subform, the following is the SQL I am playing with;

SELECT USerID, Amount,

  (SELECT Sum(tbl_Courses.Cost) AS Total

  FROM tbl_Courses

  WHERE tbl_Courses.ID <= t1.ID) AS Total

FROM tbl_Courses AS T1

But this gives me a complete running total rather than a running total by UserID and Dept.

Possible duplicate of [Trouble making a running sum in Access query](https://stackoverflow.com/questions/16347778/trouble-making-a-running-sum-in-access-query) — ashleedawg, May 26 '18 at 12:03
@ashleedawg This isn't a duplicate of that question. Here I am trying to do a running sum over UserID, and Dept not just one variable (year) as in the answer you show. — Naz, May 26 '18 at 12:35

Parfait · Answer 1 · 2018-05-26T22:25:18.937

0

Simply correlate your query to those needed groupings by adding criteria to subquery's WHERE clause and join users table to retrieve Budget value.

SELECT u.UserID, c.Date, c.Cost, 
       u.Budget -
                 (SELECT Sum(sub.Cost)
                  FROM tbl_Courses sub
                  WHERE sub.ID <= c.ID
                    AND sub.UserID = c.UserID
                    AND sub.Dept = c.Dept) AS [Budget Remaining]
FROM tbl_Users u
INNER JOIN tbl_Courses AS c
   ON u.UserID = c.UserID AND u.Dept = c.Dept

For an updateable query use a domain aggregate, DSum:

SELECT u.UserID, c.Date, c.Cost, 
       u.Budget -
       DSum("sub.Cost", "tbl_Courses", "ID <= " & c.ID & 
                                       " AND UserID = " & c.UserID &  
                                       " AND Dept = '" & c.Dept & "'") AS [Budget Remaining]
FROM tbl_Users u
INNER JOIN tbl_Courses AS c
   ON u.UserID = c.UserID AND u.Dept = c.Dept

edited May 26 '18 at 22:25

answered May 26 '18 at 18:25

Parfait

104,375
17
94
125

I tried this and although it works in the form view I can't add any more courses info to the Courses subform, is there a way around that? – Naz May 26 '18 at 20:58
Indeed. Using the correlated subquery, the overall query is not [updateable](http://allenbrowne.com/ser-61.html). You will have to use a domain aggregate, `DSum`. See edit. – Parfait May 26 '18 at 22:26
Really? Do you have some other calculation in query? If you take out `DSum` is it updateable? Look at link above of items that forces read-only. Better yet, open query directly (not in subform) and locate if a new row is possible (asterisk * showing at bottom). It could be form setup. – Parfait May 27 '18 at 00:34
No other calculations, just a test db exactly as described in the OP. You can download it [here](http://s664318443.websitehome.co.uk/database7.accdb) – Naz May 27 '18 at 11:04

Drawing down budget

1 Answers1