Составление бюджета

Question

У меня есть столик для Users который содержит бюджет на обучение

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       

У меня тогда есть другой стол Courses хранит курс, который они выбрали;

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

Я хочу создать форму с User информация сверху и подчиненная форма с Courses внизу, что-то вроде

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

[ОБНОВЛЕНИЕ] Я застрял с базовым запросом для подчиненной формы, вот SQL, с которым я играю;

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

Но это дает мне полный промежуточный итог, а не промежуточный по UserID и Dept.

Answer 1

Просто сопоставьте ваш запрос с необходимыми группировками, добавив критерии к подзапросам WHERE предложение и присоединиться к таблице пользователей, чтобы получить значение бюджета.

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

Для обновляемого запроса используйте агрегат домена, 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