Forum Discussion
Day Count Query
Is this what you mean?
SELECT D1.*,
NZ((SELECT MIN(FundingDate)
FROM tblDraws AS D2
WHERE D2.ProjID = D1.ProjID
AND D2.Type = D1.Type
AND D2.FundingDate > D1.FundingDate)-FundingDate,0) AS DayCount
FROM tblDraws AS D1
ORDER BY ProjID, Type, FundingDate;
HI Ken, that is amazing. thank you very much. The count is accurate. That is a very good observation about using the [FundingDate] vs the primary key and 100% accurate.
I have a follow up question though and wondering if you can help.
Can the result be shifted up a record as in the pic below? I fiddled around with it and its beyond my level of knowledge.
thanks again Ken.
Is this what you mean?
SELECT D1.*,
NZ((SELECT MIN(FundingDate)
FROM tblDraws AS D2
WHERE D2.ProjID = D1.ProjID
AND D2.Type = D1.Type
AND D2.FundingDate > D1.FundingDate)-FundingDate,0) AS DayCount
FROM tblDraws AS D1
ORDER BY ProjID, Type, FundingDate;
perfect! I see what you did. I understand it (a little). thank you sir!