Uncompleted tasks with number of working days OS

[TheToeOfPooh]

Tracking outstanding projects by Manager

I would like to know how\if I can use the following SQL statement -
provided by andrew93 in another thread

SELECT T_Dates.start, Weekday(Now()) AS Today, Int(Now()-[start]) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0,IIf([Today]=1,[XSDays]-1,IIf([XSDays]+1=[Today],[XSDays]-1,IIf([XSDays]<[Today]-1,[XSDays],[XSDays]-2)))) AS XSWDays, Int((Now()-[start])/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates;

Using the above example I have created a table T_Dates and included amongst others the following fields.

Name (Text)
Task (Text)
Completed (Yes/No)
Deadline (Short Format of Date time e.g. 15/03/05)

Using the field called Deadline calculate number of work days between then and today where Completed = No

Through lack of knowledge I am unable to ammend the statement.
Any sugestions please?

Bernard

 

[Andrew Fergus]

Hi Bernard
I'm not sure I am following you 100%. If you paste the SQL and then view the query design, you can add the additional fields you want (name etc) and modify the query as much as you like. Yes this will change the SQL, but the SQL I provided was just a starting point to calculate the working days (BTW, I think Norie's suggestion, whilst being a lot simpler, calculates days rather than working days). I'm also not sure why you would want to 'group by' the name - I would have thought one project name would have one deadline such that grouping was not required. However, if you do want to group the records, then try leaving the settings for the working days calculation also as 'group by'.
HTH, Andrew.

 

[TheToeOfPooh]

Andrew, Norie

Thank you both for your help. Once I had re read and digested your examples the following worked (based on a slight revision to the fields in the original table):


SELECT T_Dates.Name, T_Dates.CompleteBy, T_Dates.Task, T_Dates.Notes, Weekday(Now()) AS Today, Int(Now()-[CompleteBy]) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0,IIf([Today]=1,[XSDays]-1,IIf([XSDays]+1=[Today],[XSDays]-1,IIf([XSDays]<[Today]-1,[XSDays],[XSDays]-2)))) AS XSWDays, Int((Now()-[CompleteBy])/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates
WHERE (((T_Dates.Completed)="No"))
GROUP BY T_Dates.Name, T_Dates.CompleteBy, T_Dates.Task, T_Dates.Notes, T_Dates.Name;

Many thanks

Bernard