Solved Query or VBA function? (1 Viewer)

[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))

 

[Pat Hartman]

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

 

[CJ_London]

an alternative to

Forms!yourFormlStartDate <= CompDate OR CompDate Is Null

is

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

 

[Pat Hartman]

Not quite. I believe the default for Nz() for numeric data types is zero since it can't be "" and that would be Dec 30, 1899 in Jet/ACE. I think it might be Jan 1, 1900 in SQL Server. But in either case, it is not likely that the start date would be less than that.

 

[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

 

[Pat Hartman]

I am saying that the start date is unlikely to be < 12/30/1899 which is what you get with Nz(somedate) without an actual replacement value. When somedate is null, Nz(somedate) returns zero which is 12/30/1899 for Jet/ACE.

You can change the Nz() to return some future date or maybe even today's date depending on whether or not the dates in the table can be in the future.

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

Yes, that expression returns true but only because you are providing a default date for the NZ() and the left side of the equation is the date you are returning.

But,
StartDate <= (null, Date()) only returns true if start date is always in the past and we don't know that business rule.

 

[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

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

The pictures don't make any sense. 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. And those counts don't make sense either.

If Serial is the ID for a specific job, then it should not be included in the query if you want a count by day. The query should includ ONLY date and count.

I solved a similar counting problem for an importer. He needed to know how many boxes of material were in a warehouse on each day since that was how his warehouse bill was computed. I'm busy now and might not be able to did out the code until later today. If you don't have an anwer before 3 PM, PM me and I'll look for it.