representation in ms-access

I have a database in microsoft access. I want to know how to look up a singular datum from a reference table giving a dynamic set of values. Here is a representation of what I mean:

I have the following tables:

Points for Pushups(m):

Reps      Age 17-21,          Age 22-26,         Age 27-31

1            6                  7                    8

2            7                  9                    9

3            9                  11                  12

Fitness Tests:

Name  Reps   Test Date

Bob      2            1 jan 2009

Jill     1            5 may 2010

People:

Name         DOB

Bob      1 jan 1987

Jill     2 feb 1985

Sal      3 Mar 1991

I want the query to use People.DOB and the Test date to find the age the person was during the test. I then want the query to use this value to determine which column to look in, and the value from reps to determine which row to look in coming back with the singular value and naming it points.

for example I want bob to show

Query:

Name      DOB            Age AtTest   Reps      Points

Bob      1 Jan 1987         22         2          9

Does anyone know how to do the dynamic reference part?

I know how to make the query and I know how to get age I just don't know how to use the values as columns in the reference table, I've seen it done, but long ago and never looked into it.

The PointsForPushups table needs to be normalized first. I suggest breaking it into the two following tables:

tblPointsForPushups
----------
reps | ageGroup | pushups
1 | A | 6
1 | B | 7
1 | C | 8
2 | A | 7
2 | B | 9
2 | C | 9
3 | A | 9
3 | B | 11
3 | C | 12

tblAgeGroups
----------
ageGroup | ageMin | ageMax
A | 17 | 21
B | 22 | 26
C | 27 | 31

With these tables in place then DateDiff and Between can easily be used in a query to find the required information.

StackExchange Database Administrators Q#46201, answer score: 2