LEFT JOINs with running total (1 Viewer)

[CrystalSurfer]

Hi all,
I have 3 tables: tblMonth, tblRegulation and tblClientRegulation.
Relationships are:
tblClientRegulation.Regulation = tblRegulation.ID
and a fabricated relationship: tblClientRegulation.Month(DateEnquiryRevd) = tblMonth.ID

There is no relationship between tblRegulation and tblMonth.
I want a result that lists ALL months from tblMonth with ALL Regulations and a running count of the number of specific ClientRegulation records (detailed in subqry Y below).
eg:
Month, Reg, MonthlyTotal, RunningSum
1, 1, 5, 5
1, 2, 7, 7
2, 1, 6, 11
2, 2, 8, 15
3, 1, 3, 14
3, 2, 0, 15
4, 1, 0, 14
4, 2, 3, 18
etc.. (with all Month/Reg combinations)

However, my following query with 2 LEFT JOINS from the Month and Reg tables do not return all records when there is a 0 (zero) for a given Month/Reg combination.

How do I cater for this?

SELECT M.ID AS MonthN, X.Regulation, X.MonthTot, X.RunSum
FROM
tblMonth AS M 
LEFT JOIN 
  (
  SELECT R.Name AS Regulation, Y.MonthNo, Y.MonthTot, Y.RunSum
  FROM tblRegulation AS R 
  LEFT JOIN
    (
    SELECT CR.Regulation,
    Month(CR.DateEnquiryRecvd) AS MonthNo, 
    Count(*) AS MonthTot, 
      (
      SELECT Count(*) FROM tblClientRegulation AS CR2
      WHERE CR2.Regulation=CR.Regulation
      AND Month(CR2.DateEnquiryRecvd) <= Month(CR.DateEnquiryRecvd)
      ) AS RunSum
    FROM tblClientRegulation AS CR
    GROUP BY CR.Regulation, Month(CR.DateEnquiryRecvd)
   ORDER BY CR.Regulation, Month(CR.DateEnquiryRecvd)
  ) AS Y ON Y.Regulation = R.ID
  WHERE R.ID IN(1,2)
) AS X ON M.ID = X.MonthNo;

 

[CrystalSurfer]

partially solved..

Hi all,
I've solved my problem and am going to explain the solution to it for any future noob-let like me.
The problem was how to select all records from 2 unrelated tables and generate a running count or sum for each of the combinations. The answer was to just put them together without a join! See 8th line in code below.

Then I had a problem with the running total, in that months with a total (Tot) of zero meant the running sum (RunSum) was also zero. (See the results below the qry for months 8,9 and 10). I needed the running sum to continue to show a value, even for 0 months. (So theres a continuing line on the chart/graph based on this data). I did this by pulling the running count out of the subquery and putting it under the main SELECT clause.
This means that the query below supplies both the amount and the running sum for the Month/Regulation combination.

Hope thats useful for someone as I couldnt find a straight forward answer by searching. Or maybe it was down to my searching skills..

SELECT X.MonthNo, tblRegulation.Name, 
  (
      SELECT Count(*) FROM tblClientRegulation AS CR2
      WHERE CR2.Regulation=X.Reg
      AND Month(CR2.DateEnquiryRecvd) <= (X.MonthNo)
      ) AS RunSum
FROM (
(SELECT M.ID AS MonthNo, R.ID AS Reg FROM tblMonth AS M, tblRegulation AS R) AS X
LEFT JOIN
(
  SELECT Month(CR.DateEnquiryRecvd) AS MonthNo, CR.Regulation AS Reg, Val(Nz(Count(*),0)) AS Tot
      
  FROM tblClientRegulation AS CR 
  GROUP BY Month(CR.DateEnquiryRecvd), CR.Regulation
) AS Y ON X.MonthNo = Y.MonthNo AND X.Reg = Y.Reg)
LEFT JOIN tblRegulation ON X.Reg = tblRegulation.ID
WHERE X.Reg IN(1,2)
;

Incorrect results with zero running sum:

MonthNo	Reg	Tot	RunSum
1	1	14	14
1	2	35	35
2	1	7	21
2	2	143	178
3	1	21	42
3	2	154	332
4	1	15	57
4	2	34	366
5	1	13	70
5	2	38	404
6	1	7	77
6	2	35	439
7	1	6	83
7	2	49	488
8	1	7	90
8	2	0	0
9	1	0	0
9	2	14	502
10	1	0	0
10	2	21	523
11	1	9	99
11	2	19	542
12	1	8	107
12	2	22	564