Subselect not working

[sheetspread]

In the TVShows table I have the fields TVShow, Characters, and Age

In query 1 I have the same table with an extra column holding the subselect expression:

Expr1: (select sum(Age) from TVShows where TVShow = x.TVShow)

That works, I get the sum of ages for each show.


However, I'd like to create an independent table (called CertainTVShows) with a TVShow field where I enter the name of shows and write another query with a similar subselect that totals the ages from the original TVShows table that correspond to the shows I've entered in the CertainTVShows table. I thought it was just as simple as adding another:

Expr1: (select sum(Age) from TVShows where TVShow = x.TVShow) but it's not. I get the "Enter Parameter Value" x.TVShow question box and kind of understand why. The query won't match the TVShow fields from the different tables. How do I modify this expression to get results?

 

[sheetspread]

If what I said above is too unclear, what does the last part of:

Expr1: (select sum(Age) from TVShows where TVShow = x.TVShow)

actually mean? i.e., where TVShow = x.TVShow. Can it take whatever tv show is written in that field and match it to values in another table? I tried something similar with Dsum and couldn't get it to work either, but Giacomo says the subselect is better for queries and it looks that way, though so far I'm stuck.