Solved Query or VBA function? (1 Viewer)

[silversun]

Would help if you provided your manual calculation - I don't see why the first record must be 9.

The first project started on 11/11/2020 and ended on 12/23/2020, right? Projects number 2,3,4,5,6,7 started and ended during this period, right? Items 8 and 9 also started on 12/15/2020. As long as the days of working (from begin to finish) on a project falls on a date range then that project will be counted.
Another project as an example was item #9. It started on 12/15/2020 and ended on 12/29/2020. Within this date range I was still working on item number 1 and on the same date I started item #8. Items 10, 11, 12 and 13 also started on 12/28/2020 (falls within the range of item 9) therefore the total items I had in my bin to work on were 7 projects.
I believe you can see my logic now. I am sorry for lack of writing skills. I am trying to explain more and sometimes I make it even harder to understand.

 

[silversun]

The count of concurrent jobs should be a row on the bottom that counts by day. The count on the side should be the count of days for a SINGLE job

Hi Pat,
You could be right too. I thought if I am creating a query then a column to show the answer is easier to work on because that's how the queries work (this is my understanding, let me know if I am wrong).
Thank you for your help. I will wait for CJ_London while you are busy.
I appreciate all you do here.

 

[CJ_London]

Yes. Part of the problem is to count the number of the concurrent projects between assigning date and completion date.

contradicts

I need to find the number of the projects that are assigned to me at any assignment date and write that number in last column called concurrentPrjs.
To be more clear: I have multiple projects to work on every day concurrently and during each week I will get more projects coming and some are going out of my list by closing date.

and

I need to find the number of the projects that are assigned to me at any assignment date

you now seem to be saying you want to know how many are assigned to you and not closed during the period of each assignment. If this is the case, your query would be

SELECT  A.serial, A.assignDate, A.compDate,  COUNT(b.serial) AS concurrentPrjs
FROM  t_projects A INNER JOIN t_projects B ON B.assignDate<=nz(A.closeDate,now()) AND nz(B.closeDate,now()) >A.AssignDate
GROUP BY  A.serial, A.assignDate, A.compDate
ORDER BY A.assignDate

 

[silversun]

If this is the case, your query would be

I used this last query and I got the result as shown in image "my Table-2". The numbers are different when you do calculation by hand. The first project was in my bin from 5/10/2021 to 5/22/2021. I that period I only had three projects to work on but the query is showing 4 counts.
The second project is even more OFF because between 5/13/22021 to 7/1/2021 I had 7 projects in my bin but the query is counting 10 projects (please see "green bars" image). I am not sure where is the issue.
To make it simple, please lets be worry about closing date later and now we only count the simultaneous projects from assignDate to taskComple date.
I appreciate your time and effort to help me on this.
Masoud

 

[CJ_London]

suggest you provide the data in a form I can copy/paste to a table. I suspect the data you are showing is not all the data the query is working on. The reason for this is that you are not showing any tasks (otherwise called projects) assigned but not completed, or the close date

Otherwise modify the query - just change all instances of closedate to completiondate

 

[Pat Hartman]

Are you looking for something that says how many projects you had on each day within a period? That is what the crosstab report with the green horizontal bars looked like. If each of the green bars = 1 or true, you can add a row at the bottom to sum the 1's or -1's

If you want to specify a date and get back one number, that is a different and much simpler query.

Where Forms!yourform!specifiedDT >= ProjectStartDT And (Forms!yourform!SpecifiedDT<= ProjectEndDT OR ProjectEndDT Is Null)

 

[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\#") & ")"))