Query Results on an 'as at' date

[roarcrm]

Hello,



I have a query set up that shows a list of projects and the number of hours allocated to them. It also includes a time remaining field that subtracts the hours used in all of the tasks associated with this project.

Now, what I want to do is to edit the query so that any given date can be supplied and the results are calculated 'as at' that date.

A project may currently display 8 hours allocated with 1 hour remaining, but if I search 'as at' last week, the hours remaining may be 2 if an hours work has been done this week. Is this easy to configure? There is a field in every task called DateOfTask so I'm guessing I need to use this but just not sure how to set it up.

The SQL of the query is currently:

SELECT [TimeSpent Sum].SumOfTimeSpent, Client.ClientIdentifier, Client.Organisation, Service.ServiceType, Service.RetainedHours, Project.ProjectStatus, Project.HoursAllocated, Project.Rate, Project.RateType, [AdditionalHours Sum].SumOfAdditionalHours, [hoursallocated]-[sumoftimespent]+[sumofadditionalhours] AS OverallRemaining, Project.ProjectStatus, Client.ClientStatus
FROM (Client INNER JOIN Service ON (Client.ClientIdentifier = Service.ClientIdentifier) AND (Client.ClientIdentifier = Service.ClientIdentifier)) INNER JOIN ([AdditionalHours Sum] RIGHT JOIN ([TimeSpent Sum] RIGHT JOIN Project ON [TimeSpent Sum].Project_ProjectID = Project.ProjectID) ON [AdditionalHours Sum].Project_ProjectID = Project.ProjectID) ON (Service.ServiceID = Project.ServiceID) AND (Service.ServiceID = Project.ServiceID)
WHERE (((Service.ServiceType)="Required" Or (Service.ServiceType)="Documentation") AND ((Project.ProjectStatus)="Live"));

Thanks!

[ranman256]

Give the date and only sum the hrs up to that date.

select [project], sum([hrs]) as HrsUsed where [date]<=#" & forms!frmMain!txtDate & "#"

[roarcrm]

Give the date and only sum the hrs up to that date.

select [project], sum([hrs]) as HrsUsed where [date]<=#" & forms!frmMain!txtDate & "#"

Tried to include the project date but now the results show a line for every task rather than each project. SQL now looks like:

SELECT [TimeSpent Sum].SumOfTimeSpent, Client.ClientIdentifier, Client.Organisation, Service.ServiceType, Service.MonthlyRetainedHours, Project.ProjectStatus, Project.HoursAllocated, Project.Rate, Project.RateType, [AdditionalHours Sum].SumOfAdditionalHours, [hoursallocated]-[sumoftimespent]+[sumofadditionalhours] AS OverallRemaining, Project.ProjectStatus, Client.ClientStatus, Task.DateOfTaskFROM ((Client INNER JOIN Service ON (Client.ClientIdentifier=Service.ClientIdentifier) AND (Client.ClientIdentifier=Service.ClientIdentifier)) INNER JOIN ([AdditionalHours Sum] RIGHT JOIN ([TimeSpent Sum] INNER JOIN Project ON [TimeSpent Sum].Project_ProjectID=Project.ProjectID) ON [AdditionalHours Sum].Project_ProjectID=Project.ProjectID) ON (Service.ServiceID=Project.ServiceID) AND (Service.ServiceID=Project.ServiceID)) INNER JOIN Task ON (Task.ProjectID=Project.ProjectID) AND (Project.ProjectID=Task.ProjectID)
WHERE (((Service.ServiceType)="COMPLY As Required" Or (Service.ServiceType)="Comply Documentation") And ((Project.ProjectStatus)="Live Work") And ((Task.DateOfTask)<=Forms![COMPLY As Required Search]!StartDate));

[roarcrm]

Still struggling with this one, the query is currently returning a result for each task where I want just a result for each project. Can anybody help?

SQL is:

SELECT [TimeSpent Sum].SumOfTimeSpent, Client.ClientIdentifier, Client.Organisation, Service.ServiceType, Service.MonthlyRetainedHours, Project.ProjectStatus, Project.HoursAllocated, Project.Rate, Project.RateType, [AdditionalHours Sum].SumOfAdditionalHours, [hoursallocated]-[sumoftimespent]+[sumofadditionalhours] AS OverallRemaining, Project.ProjectStatus, Client.ClientStatus, Task.DateOfTask
FROM ((Client INNER JOIN Service ON (Client.ClientIdentifier=Service.ClientIdentifier) AND (Client.ClientIdentifier=Service.ClientIdentifier) ) INNER JOIN ([AdditionalHours Sum] RIGHT JOIN ([TimeSpent Sum] RIGHT JOIN Project ON [TimeSpent Sum].Project_ProjectID=Project.ProjectID) ON [AdditionalHours Sum].Project_ProjectID=Project.ProjectID) ON (Service.ServiceID=Project.ServiceID) AND (Service.ServiceID=Project.ServiceID)) LEFT JOIN Task ON (Task.ProjectID=Project.ProjectID) AND (Project.ProjectID=Task.ProjectID)
WHERE (((Service.ServiceType)="COMPLY As Required" Or (Service.ServiceType)="Comply Documentation") And ((Project.ProjectStatus)="Live Work") And ((Task.DateOfTask)<=Forms![COMPLY As Required Search]!StartDate));