Getting Sum() Of Second Table

[Dave Punky]

Hi all,

I'm trying to do something that I think should be simple but seems to be presenting more issues than I anticipated.

Basically I have two tables, which look a bit like so:

Table A

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Title[/TD]
[TD]Type[/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[TD]Table A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]A2[/TD]
[TD]Table A[/TD]
[/TR]
</tbody>[/TABLE]

Table B

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Searched ID[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

I've been trying to write a query that sums the results of Table B and groups by the Searched ID basically creates a recordset which would look like so with the information above:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Title[/TD]
[TD]Type[/TD]
[TD]Table[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[TD]Table A[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]A2[/TD]
[TD]Table A[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


My current SQL query is like so:

SELECT [ID], [Title], [Type],  [Table], (SELECT Sum([Table B.Result]) AS sum FROM Table B WHERE [Table A.ID]=[Table B.Searched ID]) AS [Result] 
FROM Table A 
WHERE [Title] LIKE "*A*" 
ORDER BY [Result] DESC, [Title] ASC;

I've got this working over a union query for multiple tables, however when trying to just look at these two tables Access is expecting an operator for [Result] that I can't seem to account for. I'm actually sending the query via VBA from Excel so it just errors advising it doesn't have the minimum parameters.

I'm almost certain I'm doing something obviously wrong but I can't seem to quite work it out - any advice on what I've done wrong in my query above? I've tried doing a left join as well from Table A to B but I'm having issues grouping the ID's together in Table B to subsequently attach them to the initial table.

 

[alansidman]

Try this instead:

SELECT [Table B].[Searched ID], [Table A].Title, [Table A].Type, [Table A].Table, Sum([Table B].Result) AS SumOfResult
FROM [Table A] INNER JOIN [Table B] ON [Table A].ID = [Table B].[Searched ID]
GROUP BY [Table B].[Searched ID], [Table A].Title, [Table A].Type, [Table A].Table;

This is what my results look like:

1	A	A1	Table A	20
2	B	A2	Table A	10

<caption> Query1 </caption><thead>
[TH="bgcolor: #c0c0c0"] Searched ID [/TH]
[TH="bgcolor: #c0c0c0"] Title [/TH]
[TH="bgcolor: #c0c0c0"] Type [/TH]
[TH="bgcolor: #c0c0c0"] Table [/TH]
[TH="bgcolor: #c0c0c0"] SumOfResult [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

 

[Dave Punky]

Hi Alan,

First of all that solution has worked for me after adding some WHERE criterial, so thank you very much for that.

However I'm trying to do an ORDER BY the SumOfResult and I'm encountering the same issues as before where Access expects a value for SumOfResult (and therefore I get a too few parameters met error via VBA).

To put into perspective I am just trying to add the following to the end of your statement:

ORDER BY [SumOfResult] DESC, [Title] ASC;

I feel like I'm missing something obvious but I can't quite put my finger on it as this would just work with a regular statement.

 

[Dave Punky]

Not ideal to bump the thread but has anyone got any ideas regarding my second point?

 

[xenou]

Instead of:

ORDER BY [SumOfResult] DESC, [Title] ASC;

Try:

ORDER BY Sum([Table B].Result), [Title] ASC;

Usually you cannot assume SQL knows what an alias in the SELECT clause means.


------------------------------------


Note that you *could* use the column alias if you nest the query in a subquery and then "query the query":

SELECT
	T.[Searched ID],
	T.Title,
	T.[Type],
	T.[Table],
	T.SumOfResult 
FROM
(
	SELECT 
		[Table B].[Searched ID], 
		[Table A].Title,
		[Table A].[Type], 
		[Table A].[Table], 
		Sum([Table B].Result) AS SumOfResult
	FROM 
		[Table A] 
		INNER JOIN 
		[Table B] ON [Table A].ID = [Table B].[Searched ID]
	GROUP BY 
		[Table B].[Searched ID], 
		[Table A].Title, 
		[Table A].[Type], 
		[Table A].[Table]
) AS T

ORDER BY T.SumOfResult

 

[Dave Punky]

Thanks so much xenou that's exactly what I needed, and thanks for explaining so coherently as well. I knew it must have been something obvious but I never would have guessed that!

 

[Dave Punky]

Ok I've encountered another issue with this query - in the event there are no results in Table B, I still need it to display the results of Table A (but with a value of 0 for the sum). At the moment it's not displaying the results (because its displaying the ID information from Table B rather than Table A).

I did the obvious thing of changing the ID reference in the table from Table B to Table A but its still not including results which would have a null result from Table B. I've tried adding in the following to the sum but it's made no difference...

...[Table A.Table], IIF(ISNULL(Sum([Table B.Result]),0,Sum([Table B.Result])) AS SumOfResult

 

[xenou]

Hi,
Change INNER JOIN to LEFT JOIN. That would be the usual solution. Let me know if it doesn't work (if not, post your complete query as it stands now).

With a LEFT JOIN, all fields from the table on the left side are returned, with matched fields from the table on the right side, if there are any. If the table on the right side doesn't have a match, you get Nulls instead. Note that Sum will ignore Nulls, so it shouldn't affect the outcome (you can think of the missing records as being zeros). By left and right side, we just mean that A LEFT JOIN B has Table A on the left and Table B on the right; so all of A's records are returned with any matching B records.