OK, all you smart people, here’s what I’m trying to do (no flaming in the comments that I should just use Excel – there are reasons for my madness!):
In Access, I’ve got a table that lists metro area zip codes and which office serves that zip code. Additionally, I have two fields of numerical data (population stats) for each zip code. I can set up a query that runs the calculations needed (basically multiplication or division), but only for one of the counties, because I can’t figure out how to use a variable sum instead of a constant. Let’s say the table’s set up this way:
Column A: Zip Code
Column B: Office (that serves the zip code)
Column C: Population
Column D: Subpopulation (# of people within that population that have disabilities)
Now, I can make the following:
Column E: Zip Code’s percent of county’s population (Column C div by Sum for County, a constant I entered into the equation)
But I’d like to make Column E more universal… Column C div by a variable sub depending on which county the zip code is part of. If I assign all the zip codes to a county, I can create a report that totals the populations by county. But how do I pull that number into the equation as a variable? Of this, I have no idea. Maybe it’s not possible, but I feel like it should be. I just don’t know how to get there.
Did I even explain the problem in a way that makes sense? I don’t know.