I'm not very strong with queries, but normally manage to find a way round the problem, but I have hit a brick wall with this one and need some help.
I am making a system to tack site audits, each client can have multiple sites, and each site can have multiple audits assigned, and the tables are set up accordingly. In addition in tblAudits there is also a ClientID FK as well as SiteID FK in the hope it would make life easier but it hasn't.
Each Site has a stage associated with it, from tblStage. All the stages are listed in tblStage and identified by ForStage column, 1 = Client, 2 = Site, 3 = Audit, each stage has a description and a 'PercentComplete' column as well.
What I am trying to do is create a front view, which gives an overview of what % complete each client is at for the various stages, so for sites it is SUM(PercentComplete from tblStage)/no sites for that client, and the same for audits, but / by no audits.
I have this working in the query as laid out below, but it only works so long as there are sites and audits assigned to a client, if there aren't it removes them from the results, I need this to be a complete list of clients not partial, and if there are no sites so 0 and if there are no audits show 0, I have tried everything I can think of, but I am not getting anywhere.
Main query:
qryPercentSiteComplete:
Can anyone please help shed some light on this please?
I am making a system to tack site audits, each client can have multiple sites, and each site can have multiple audits assigned, and the tables are set up accordingly. In addition in tblAudits there is also a ClientID FK as well as SiteID FK in the hope it would make life easier but it hasn't.
Each Site has a stage associated with it, from tblStage. All the stages are listed in tblStage and identified by ForStage column, 1 = Client, 2 = Site, 3 = Audit, each stage has a description and a 'PercentComplete' column as well.
What I am trying to do is create a front view, which gives an overview of what % complete each client is at for the various stages, so for sites it is SUM(PercentComplete from tblStage)/no sites for that client, and the same for audits, but / by no audits.
I have this working in the query as laid out below, but it only works so long as there are sites and audits assigned to a client, if there aren't it removes them from the results, I need this to be a complete list of clients not partial, and if there are no sites so 0 and if there are no audits show 0, I have tried everything I can think of, but I am not getting anywhere.
Main query:
Code:
SELECT tblClients.ID, tblClients.CarbonReference AS [Job Ref], tblClients.CompanyName AS Client, [FirstName] & " " & [Surname] AS Owner, tblClients.Stage1Deadline AS [S1 Deadline], tblStage.PercentComplete AS [S1 %], tblClients.Stage2Deadline AS [S2 Deadline], 1*[sitepercentcomp]/DCount("[SiteID]","tblSites","[ClientID] = " & [tblClients].[ID] & "") AS [S2 %]
FROM tblStatus INNER JOIN (qryPercentSiteComplete INNER JOIN ((tblClients INNER JOIN tblUsers ON tblClients.OwnerID = tblUsers.ID) INNER JOIN tblStage ON tblClients.Stage1StageID = tblStage.StageID) ON qryPercentSiteComplete.ClientID = tblClients.ID) ON tblStatus.StatusID = tblClients.Stage2StageID
ORDER BY tblClients.CompanyName;
qryPercentSiteComplete:
Code:
SELECT tblSites.ClientID, Sum(tblStage.PercentComplete) AS SitePerCentComp
FROM tblStage INNER JOIN tblSites ON tblStage.StageID = tblSites.Stage2StageID
GROUP BY tblSites.ClientID;
Can anyone please help shed some light on this please?