Query Results on a given date

[RoarCRM]

Hello,

I have a problem that I'm struggling with. In our database we have services, within services we have projects and within projects we have tasks.

I want to display all projects that are currently 'Live' and show the results on a given date. So if I select 1 May 2014 I want only tasks completed before this date to be included in the calculations of how many hours have been used. What I don't want is for the query to return a row for every task which it is currently doing, I have been playing around but cannot seem to get this to work. Any ideas? The SQL of my query currently is below.

SQL:

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
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 OUTER JOIN Task ON (Project.ProjectID = Task.ProjectID) AND (Task.ProjectID = Project.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]));

 

[jdraw]

Please post a screen capture of your Relationships window and post it in zip format.

You really have to get your tables and relationships set up and tested before getting in to queries, forms etc.

From your post, these seems to be the set up
Service-->Project-->Task

You should have numeric Identifiers on ServiceType (ServiceTypeId) to avoid string compares

DateDiff() will probably be useful

Good luck

 

[RoarCRM]

Won't let me post an image/attachment as I don't have 10 posts.

Basically it's:

Client -----< Service ------< Project------< Task

The issue I'm having is that when I include Task in the query in order to try and search by task date it creates a new row in the query results for every task but I only want to see projects.