CrystalSurfer
Matrix activist
- Local time
- Today, 11:14
- Joined
- Jan 11, 2006
- Messages
- 75
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?
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?
Code:
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;