Day Count Query

[Tony2021]

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

[#2]

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.

[#3]

@Ken_Sheridan 

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.  

[#4 · Accepted solution]

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;

[#5]

@Ken_Sheridan 

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