I'm using this SQL string:
[vba]
SELECT tblProjects.School, 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) ON (tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) And (tblLocalAuthority.[Local Authority] = tblProjects.[Local Authority])) INNER JOIN [tblDFC/School] ON (tblLocalAuthority.[Local Authority] = [tblDFC/School].[Local Authority]) And (tblSchools.[School Number] = [tblDFC/School].School)) INNER JOIN tblInvoice ON (tblLocalAuthority.[Local Authority] = tblInvoice.LocalAuthority) And (tblSchools.[School Number] = tblInvoice.School) And (tblProjects.[Project Number] = tblInvoice.Project)
GROUP BY tblProjects.School;
[/vba]
I need all the tables present here, because I once I've got this part of the query right I'll be getting things from them too.
The query seems to work fine until I add [tblDFC/School] and tblInvoice. When I add them, however, the figures for "Amount Committed" and "Amount in Progress" become totally different. I wonder if someone could tell me where I'm going wrong?
If you need any more background this thread is a continuation of this previous thread in "Access Help": http://vbaexpress.com/forum/showthread.php?t=19051