Query Help (1 Viewer)

[Acropolis]

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:

 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:

 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?

 

[fvdb]

Give a look at the NZ function.

This will transfrom null values to 0 (or to the one wich is defined)

 

[Acropolis]

Tried NZ in various places but to no avail, just can't get it working, know I am probably only missing something simple, but can't seem to get there.

 

[fvdb]

Can you upload a stripped down version of your DB?

 

[CJ_London]

access cannot create records where none exist. you need to have a 'primary' table which lists all the records and left join it to the other tables (rather than inner join)

It is not clear from your code which is the primary table (perhaps tblclients?) but you may need more than one left join and reorganise your query joins to avoid conflicts

this link will help explain about left joins http://www.w3resource.com/sql/joins/perform-a-left-join.php

 

[Acropolis]

Zipped version is attached.

 

[Acropolis]

Changing the join to "Include ALL records from 'tblClient' and only those records from 'qrypercentsitecomplete' where the joined fields are equal" solved the problem instantly.

Joins is something I really need to get my head round properly as I have never looked into them much, and I can see it will make life a lot easier than the way I currently do things... always learning

 

[fvdb]

Some problems with your JOINS, you only had one-to-one JOINS and therefore it only shows records if it has data in other linked tables. I changed qryMainClients and now have more results.

You really should set up some relations for your tables

PS: If you not want the warnings that you add a value to a table you can put docmd.setwarnings false before the sql line you run

 

[Acropolis]

Thanks, this has been doing my had in since yesterday afternoon you are all life savers.

With regards the relations, when I first started doing these, I used to set them up as I created the tables using the relationships tab, but then for some reason stopped. I always have the intention of going through and doing them, but never seem to get round to it, does it make a massive difference? or is it just quicker when creating queries?

I don't normally use an Access backend, I normally have backends on MySQL, but this is only going to be used for a couple of months, but quite a few people, and the hassle of getting IT to set up all the ODBC connectors is a nightmare. Is there perhaps a way of doing it without ODBC connectors easily?

I don't normally get the warnings on my machine as I turn them off in client settings so don't notice them, but this was something I was going to look at for this DB so I don't have to go round each machine and turn them off, does docmd.setwarning false have to go before each line of code, or can I just turn them off when the DB opens and they stay off?

Thanks again

 

[fvdb]

Docmd.setwarnings false will disable them all. Put it in the OnLoad Event of the mainform you open when you start the DB

See here more about relationships. It's more important for keeping the data right in your DB (Duplicate Data, Orphan records, ...)

https://support.office.com/en-us/ar...ionships-30446197-4fbe-457b-b992-2f6fb812b58f