PDA

View Full Version : Sums change when I add Inner Joins



icthus123
04-21-2008, 05:17 AM
I'm using this SQL string:



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;


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

matthewspatrick
04-21-2008, 09:16 AM
[...] 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?

Well, any time you do an INNER JOIN, you restrict the result set to rows where each entry in TableA has a corresponding entry in TableB. My guess is that [tblDFC/School] and tblInvoice do not have entries for everything you were expecting to see from the other tables.

One way to deal with that is by using outer joins (LEFT JOIN or RIGHT JOIN), but Jet SQL does not like seeing INNER JOIN and an outer join in the same SELECT, so you will probably have to either use subqueries or break it up into separate queries.

BTW, it's terrible practice to use saces or punctuation in table or field names...

icthus123
05-08-2008, 06:02 AM
Well, any time you do an INNER JOIN, you restrict the result set to rows where each entry in TableA has a corresponding entry in TableB. My guess is that [tblDFC/School] and tblInvoice do not have entries for everything you were expecting to see from the other tables.

One way to deal with that is by using outer joins (LEFT JOIN or RIGHT JOIN), but Jet SQL does not like seeing INNER JOIN and an outer join in the same SELECT, so you will probably have to either use subqueries or break it up into separate queries.

BTW, it's terrible practice to use saces or punctuation in table or field names...

How would I use LEFT JOIN or RIGHT JOIN instead? Is it just a case of replacing INNER with LEFT or RIGHT?

Is it? I'll have to stop doing that in future then! :confused4

matthewspatrick
05-08-2008, 06:07 AM
How would I use LEFT JOIN or RIGHT JOIN instead? Is it just a case of replacing INNER with LEFT or RIGHT?

Without knowing anything about the structure or contents of your database, that would be difficult to say...


Is it? I'll have to stop doing that in future then! :confused4

No worries. Access's documentation does little to guide users in terms of design best practices. I think this particular lesson is one that all users have to learn the hard way... :writer:

icthus123
05-08-2008, 07:47 AM
Without knowing anything about the structure or contents of your database, that would be difficult to say...

What are the general rules?

matthewspatrick
05-08-2008, 10:44 AM
What are the general rules?

When joining tables...


FROM Table1 INNER JOIN Table2 ON ...

will return only rows where the joined column(s) in the tables are equal.


FROM Table1 LEFT JOIN Table2 ON ...

will return all rows from the "left" table (in this case, Table1), and only those rows from the other table where the joined column(s) are equal.


FROM Table1 RIGHT JOIN Table2 ON ...

will return all rows from the "right" table (in this case, Table2), and only those rows from the other table where the joined column(s) are equal.

icthus123
05-14-2008, 02:01 AM
When joining tables...


FROM Table1 INNER JOIN Table2 ON ...

will return only rows where the joined column(s) in the tables are equal.


FROM Table1 LEFT JOIN Table2 ON ...

will return all rows from the "left" table (in this case, Table1), and only those rows from the other table where the joined column(s) are equal.


FROM Table1 RIGHT JOIN Table2 ON ...

will return all rows from the "right" table (in this case, Table2), and only those rows from the other table where the joined column(s) are equal.

Okay I think I've sorted that. Now it's telling me to create a query to include in my query to do that. How do I include a query within a query?

matthewspatrick
05-14-2008, 02:13 AM
Okay I think I've sorted that. Now it's telling me to create a query to include in my query to do that. How do I include a query within a query?

Create and save your first query. Now you can refer to that query as if it were a table.