SOLVED

# Day Count Query

Steel Contributor

# Day Count Query

Hello Experts,

I am having issues making a day count query for 3 criteria.

I have a 1 table with tblDraws.[FundingDate] and I need to make a day count between the [FundingDate] but the criteria I need is where ProjID = ProjID and Type = Type and [ID] < the current [ID].

So there are 3 criteria (although I might be able to get by with only 2 criteria).  It is a little hard to explain but if you look at the attached excel I think it is  easier to understand.  Its not just a simple day count from the current record and the previous record based on [ID]...I need to consider the other 2 criteria.

I have attached a sample db with only 1 table [tblDraws] and an example excel file for what I am looking for.

Grateful for the help.

Let me know if not clear.

thank you

4 Replies

# Re: Day Count Query

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.

# Re: Day Count Query

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.

best response confirmed by Tony2021 (Steel Contributor)
Solution

# Re: 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;

# Re: Day Count Query

perfect!  I see what you did.   I understand it (a little).   thank you sir!

1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

# Re: 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;