Solved Query or VBA function?

[silversun]

Hi all
I have a simple database with a simple table. Table t_projects has a column called serial and four more columns that are called assignDate, compDate (completion date), closeDate and concurrentPrjs . This table can have too many rows but I think it doesn't matter.
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.

My question:
What would be the best approach to count the number of projects at any given assign date? VBA or query?
I am not sure how to do the query in this case while I think it can be easier if VBA function(s) was used.

Please let me know the easiest and simplest way of doing so.
Please let me know if I am not clear enough.
Thanks

 

[pbaldy]

I would not save it. It can easily be counted via a query or DCount() function. I suspect if you used VBA you'd be using one of those anyway.

 

[pbaldy]

Oh, and in either method your criteria would compare the start date to the given date (<=) and the end date >= the given date.

 

[silversun]

Oh, and in either method your criteria would compare the start date to the given date (<=) and the end date >= the given date.

Thanks for the reply,
I wrote a function that accesses the table and can count the number of the projects that are in the same date range as the first one. I am sure if I create a loop then it can repeat the same process in all rows (I haven't done that yet). They will work like two nested loops.
As in your first reply I would like to use a query that can perform this calculation whenever needed instead of saving it in the table, but how? That part is the part that I have no idea how.
Please help.
Thank you

 

[arnelgp]

as suggested, you don't need to save the "count" of projects for a given date range.
you can use Total query:

select Count("1") As CountOfProjects from t_projects where assignDate Between [start_date] And [end_date];

 

[silversun]

as suggested, you don't need to save the "count" of projects for a given date range.
you can use Total query:

select Count("1") As CountOfProjects from t_projects where assignDate Between [start_date] And [end_date];

Thanks for the help.
I will try to create the query as you suggested and if I couldn't make it run I will get back to you.
Thank you

 

[Cronk]

Both answers given do not provide for the situation where the projects are on going ie there is no end date yet. So the criteria needs to be
StartDate <= GivenDate and (CompleteDate > GivenDate AND isNull(EndDate))

 

[CJ_London]

an alternative to

Forms!yourFormlStartDate <= CompDate OR CompDate Is Null

is

Forms!yourFormlStartDate <= nz(CompDate,Forms!yourFormlStartDate)

 

[CJ_London]

not sure what you are saying. The code says

somedate is less than or equal to if the control is null use somedate otherwise use the control date

?date()<=nz(null,date())
True

 

[silversun]

I'm not sure what the difference between CompDate and CloseDate. But I would suggest
Where (Forms!yourform!EndDate >= AssignDate and (Forms!yourFormlStartDate <= CompDate OR CompDate IsNull)) AND AssignedTo = Forms!YourFormYourID

Hi,
Regarding variable: compDate; when I finish my work on a project I send it to upper manager on compDate (completion date). He then needs time to review and close the case. It can be closed in the same day or days after I complete my work.
I am still not quite understood all your codes from everybody.
Thanks

 

[silversun]

as suggested, you don't need to save the "count" of projects for a given date range.
you can use Total query:

select Count("1") As CountOfProjects from t_projects where assignDate Between [start_date] And [end_date];

I was not able to create a count query with date-range criteria . Please see the attached image and advise if you can.
I appreciate it.
Thank you

 

[silversun]

I would not save it. It can easily be counted via a query or DCount() function. I suspect if you used VBA you'd be using one of those anyway.

I am not sure how to create the query in this case. I appreciate if you could walk me through the process. Thank you

 

[silversun]

I believe you were able to create the query. It just didn't produce the results you expected. Computers are annoyingly precise. We also need precision when you describe a problem. It didn't work just doesn't work for us.

I'm going to guess that the problem is that the wrong data (or no data) is being returned. Looking at the picture, I see that you have put two conditions on separate lines. The way that the QBE works is like a race track. All the conditions on ONE line are AND'd but the conditions on different rows are OR'd. Since you need an AND relational operator, you need the conditions to be on the same line. You can create a second column for the date field but uncheck the show button but since there are only the two conditions, most people would put them in the same cell. So:

> [assignDate] AND <= [taskComplete]

as written, your expression is > [assignDate] OR <= [taskComplete]

However, assignDate is NEVER going to be < assignDate so there is something wrong with the logic. Are the variables supposed to come from a form? If so, then

>Forms!yourform![txtassignDate] AND <=Forms!yourform![txttaskComplete]

I use prefixes for form controls so that in VBA, I know I am referring to a control. In this context it is clear because you are qualifying the variable names with "Forms!yourform".

I'm not sure what the point of Count("1") is except to be confusing. Most people would use Count([yourfieldname]). This would give you a count of non-null values for [yourfieldname]. To get a straight row count regardless if a particular field is null or not, the standard method is Count("*") which I assume is what the Count("1") is intended to do.

Hi Pat,
Thanks for the reply.
There is something missing in my query that I am not even aware of that. I try to explain my problem here again so that you can help me better.
I am trying to count the number of the simultaneous assigned projects. For example if you look at the attached image from a sample excel sheet "myTable", the count in first case must show 9 and in second case it must show 6. The count of Concurrent Jobs should be 5 when I am working on serial number 9748623, the last item in the table . (All numbers here are calculated manually). Do you see my logic?
I have another screenshot "orange bars" showing orange colored cells representing duration of each project from assignment date to task-complete date.
Screenshot "orange bar 2" shows another example. I have 7 concurrent tasks when I had item number 9 (ser #: 9521338) in my hand between 12/15/2020 and 12/29/2020.
I hope I've explained myself clearly.

When I used my conditions in Totals Query it was giving me error message that I couldn't understand it!
Please see the image attached, "my query".

You were asking if my variables are coming from a form. The answer is NO. I want to run the query when I am opening a report/form to see the results in one of the controls dynamically. Clear?

If you think there is a better way of doing it please show me how.

 

[CJ_London]

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.

To clarify - a project is assigned to you, completed and then closed. And you want to know how many are assigned to you and not closed on any given date - and based on your initial post and post#17 a given date is is actually a range of dates which are your assigned dates. The orange bars start on assigned date and end on the completion date, but you do not show the closed date (which visually would be helpful)

(All numbers here are calculated manually). Do you see my logic?

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

It doesn't help that your explanation does not fit the images - in the first image record 9 is 9521338 and in the second 4530218 (9521338 appears in record 13). And unless some are closed, I count 9, not 7. And I don't see why 6003458 would have a different count - they were assigned on the same day - unless there is a time element involved as well.

I'm going to take a wild stab and say you need to use a subquery or a non standard join (which will be faster if your fields are indexed) and that your date fields contain a time element

The subquery method

SELECT  serial, assignDate, compDate, (SELECT COUNT(*) FROM t_projects B WHERE assignDate<=A.assignDate AND nz(closeDate,now()) >A.AssignDate) AS concurrentPrjs
FROM  t_projects A
ORDER BY AssignDate

the non standard join method (can only be written in SQL)

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

 

[silversun]

To clarify - a project is assigned to you, completed and then closed. And you want to know how many are assigned to you and not closed on any given date - and based on your initial post and post#17 a given date is is actually a range of dates which are your assigned dates. The orange bars start on assigned date and end on the completion date, but you do not show the closed date (which visually would be helpful)

Yes. Part of the problem is to count the number of the concurrent projects between assigning date and completion date.
I was trying to keep my table and explanations as simple as I could. I thought if I needed to include the closing date then I can apply the method you teach me to closing date as well. Usually the closing date is the same as taskComplete date or it happens within 2-3 days.

 

[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