Aggregate From Different Domains

[bugme]

I have two tables, "Counties" and "Cities", that list all of the counties and municipalities in a given U.S. state. The "Counties" table has fields "CountyName" (the name of the county, predictably) and "CountyPop" (its population). The "Cities" table has fields "City Name" (the name of the city), "County" (the county in which the city is located, populated through a lookup to [Counties]![CountyName]), and "CityPop" (the population of the city).



What I would like to do is determine for each county the population not residing in a city (the population of the county minus the total population of all cities within its borders). In other words, for each entry in [Counties]![CountyName], I want to subtract from [Counties]![CountyPop] the sum of [Cities]![CityPop] where [Cities]![County] equals [Counties]![CountyName]. Domain aggregate expressions don't work because the two values needed for the criteria are in different domains. I suspect the answer is in SQL, but I haven't reached that level yet.

Any help would be much appreciated.

[offie]

You'll want a totals query, dont worry it doesn't involve much sql at all.
Note: It's not [Table]![Field] it is [Table].[Field]

So Click on the totals button in the ribbon
Add the tables, add a relatioship
Select fields County, CountyPop, CityPop
For CityPop change Group BY to Sum.
*Makes sure that County is Group By

Now go intol SQL view to copy some stuff,
Copy Sum([City].[CityPop])

Go back to design view
In a new column, in the Field Row enter:
Name: [County].[CountyPop]-Sum([City].[CityPop])

*You technically don't need to select CountyPop and CityPop but I selected them to copy the name (to be sure there are no spelling mistakes) and when I'm done I delete them

That should work, let me know if anything funky happens.