Solved Query or VBA function?

[silversun]

Are you looking for something that says how many projects you had on each day within a period?

Not each day. I am counting the projects in my hand based on each serial number. For example it takes 23 days to finish the project with serial number 123456 (6th row in green bars) and within this 23 days I worked on 4 more projects (rows 2, 4, 5 and 7) that may or may not have been finished. Do you see the logic now?

Here I have created a sample database with a new table that has more rows and from now on we will use it until we get the right query.
Please see the attached database and run the query. The calculated numbers are not what I am expecting to see.

Sorry for late respond to your question. I have my limitations when I am at work, made me to create a sample DB from scratch.

 

[arnelgp]

so what do you expect? any sample output?
see query1.

 

[silversun]

so what do you expect? any sample output?
see query1.

Hi Arnelgp,
Thanks for the help. Your query is excluding the projects that were started before assign date of each project, please see the attached image.
Project #2 was started on 6/23/2020 and ended on 8/7/2020.
In this time frame I worked on 12 more projects. I was expecting to see 13 as your query output. They are all marked with a star.
No matter what if they were finished or not. As long as they were started, finished or were current project they must be counted.
This is a very minor change if you can modify your query.
Thank you

 

[arnelgp]

see query1 again.

 

[silversun]

see query1 again.

Problem solved. Thank you very much.
I appreciate all your helps.

 

[silversun]

see query1 again.

Can you please explain what's going on with the formula you wrote in the last column?

concurrent: CInt(DCount("1","t_projects","([compDate] Between " & Format([assignDate],"\#mm\/dd\/yyyy\#") & " and " & Format([compDate],"\#mm\/dd\/yyyy\#") & ") Or ([assignDate] Between " & Format([assignDate],"\#mm\/dd\/yyyy\#") & " and " & Format([compDate],"\#mm\/dd\/yyyy\#") & ") Or ([assignDate] <=" & Format([assignDate],"\#mm\/dd\/yyyy\#") & " and [compdate] >= " & Format([compDate],"\#mm\/dd\/yyyy\#") & ")"))