having issues with Average function in query

kitranet

New member
Local time
Today, 21:45
Joined
Jul 5, 2013
Messages
3
Hi
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard

Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status

The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon

I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013.

My Dashboard needs to have the below fields

Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days

I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero. below is the query which i developed any guidance towards the solution will be greatly appreciated. Note "Operational* Categorization Tier 2" is the project type

SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
 
It is very hard to read your query with all the noise of the embedded spaces and special characters which force the use of the square brackets. Good practice is to use only letters, numbers and the underscore in table and column names so you never have to use square brackets.

Count operates on the entire set of data returned by the select clause so the only thing you can ever "count" with them is the number of rows or rows with non-null values in a particular field. So - Count(*) counts all rows and Count(SomeFieldName) counts the number of non-null instances of SomeFieldName which is almost always = to the count of all rows.

If you want to "count" values, you can sometimes accomplish it by using Sum() with an IIf()

Sum(IIf(SomeField = "xxx", 1, 0)) As xxxValues

"" and Null are not the same thing. If your data may contain ZLS (Zero-Length-Strings) then test for = "" but if you want to include/exclude nulls you must use "Is Null" or "Is Not Null".

If Status might be null, you need to decide whether you want to include nulls or exclude them. The test as it exists will ignore null values so it may be operating as you expect but it is an accident.
 
used the combination of sum & IIF and worked like charm, thanks again
 
Forget about queries for the moment. Consider the following trivial example
Project 1 Start: 1 January 2013 Finish: 31 January 2013 Days: 31
Project 2 Start: 1 January 2013 Finish: Null (
because it's not yet completed) Days: Unknown

What is the average time for completion? I'd say meaningless.

I think you'd need to have an expected date of completion.
 

Users who are viewing this thread

Back
Top Bottom