having access reference 2 records and come back with a specified value in another record

B

Beth Baar

I am new to Access.

I have been using a spreadsheet to do compliance checks on rental
units. The way I've worked it, is I had a cell reference another cell
with the number of people in the household, then reference another
cell that held the county the family lived in. The formula would then
reference a second worksheet that held income guidelines where the
percentage of poverty was calculated. The formulas I've been using
are below:

=if(County="Marquette", 'HUD Icome'!B3, (if(County="OUtagamie", 'HUD
Income'!B12, (if(county="portage", 'HUD Income'B21,
(If(County="Waupaca", 'HUD Income'!B30, (if(County="Waushara", 'HUD
INcome'!B39)))))))))))

And the cells that were referenced (B3, B12, B21, etc) would hold a
formula like this:

=If(HHSize=1, 'HUD Income'!B44, (if(HHSize=2, 'HUD INcome'!D44,
(If(HHSize=4, 'HUD Income'!E44, (if(HHSize=5, 'HUD Income'!F44,
(if(HHSize=6, 'HUD Income'!G44, (if(HHsize=7, 'HUD INcome'!H44,
(if(HHSize=8, 'HUD INcome'!I44, ""))))))))))))

Can anyone help me out as to how to reference two records (county and
household size) and have them come up with a specific record naming
the income limits? I'm trying to do this in a report. Is that wrong?
 
Top