PDA

View Full Version : INNER JOIN problem



icthus123
09-01-2008, 02:03 AM
I posted on this a while back, but I've only just started working on it again. I have a query with SQL as follows:

SELECT tblLocalAuthority.[LA Number], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblProjects.School, tblSchools.[School Name], Format(nz(Sum(Switch([tblProjects].[Status]="Pending",[tblProjects].[DFC Y1],[tblProjects].[Status]="Approved" Or "Final" Or "Exceptionally Approved",0)),0),'?#,##0.00') AS [Amount in Progress], Format(nz(Sum(Switch([tblProjects].[Status]="Pending",0,[tblProjects].[Status]="Approved" Or "Final" Or "Exceptionally Approved",[tblProjects].[DFC Y1])),0),'?#,##0.00') AS [Amount Committed]
FROM tblLocalAuthority INNER JOIN ((tblSchools INNER JOIN tblProjects ON tblSchools.[School Number] = tblProjects.School) INNER JOIN [tblDFC/School] ON tblSchools.[School Number] = [tblDFC/School].School) ON (tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) AND (tblLocalAuthority.[Local Authority] = tblProjects.[Local Authority]) AND (tblLocalAuthority.[Local Authority] = [tblDFC/School].[Local Authority])
WHERE (((tblProjects.[Date Submitted]) Between #4/1/2007# And #3/31/2008#))
GROUP BY tblLocalAuthority.[LA Number], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblProjects.School, tblSchools.[School Name];

Now the query works fine until I add the table tblDFC/School when all the sums suddenly go wrong. What is the reason for this? :dunno