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;
I think this is what you have in mind:
SELECT D1.*, NZ(FundingDate -
(SELECT MAX(FundingDate)
FROM tblDraws AS D2
WHERE D2.ProjID = D1.ProjID
AND D2.Type = D1.Type
AND D2.FundingDate < D1.FundingDate),0) AS DayCount
FROM tblDraws AS D1;
Note that using the autonumber primary key to determine the chronological sequence of rows might not be reliable. Instead use the FundingDate column.
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!