Join table to aggregate

[Timothy_Keitt]

I am trying to figure out how to have two columns in some sort of table or report where one is drawn from one table and another is matched according to an aggregate on another table. In SQL, this would roughly look like

SELECT a.name, a.target, sum(b.value) FROM TAB1 a, TAB2 b WHERE a.name = b.name GROUP BY a.name

Here, unique names occur once in a but many times in b so that many rows of b match to each row of a.

TAB1

Name. Target
A 1
B 2
C 3

TAB2

Name Value
A. 5
A 3
B 2
C 3
B 6
C 4

[Timothy_Keitt · Accepted solution]

I see now that something like this is possible by linking from TAB1 to TAB2 and then adding a rollup field.

This works but it requires manually linking the two tables. This seems a mind-boggling omission in that there is no way I can see to link the tables based on a condition. Is it true that there is no SQL WHERE equivalent? It seems such an obvious thing to extend linking with a conditional expression matching two fields between tables.