Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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


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


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.

share|improve this question
add comment

1 Answer

Try this query. It uses the datediff function to compute the age. Then it uses a switch statement to compare it to the point columns.

SELECT tblPerson.PersonName, 
       DateDiff("yyyy",[DOB],[TestDate]) AS Age, 
       tblFitnessTests.RepID AS Reps, 
       Switch([Age]>=17 And [Age]<=21,[Point1721],[Age]>=22 And [Age]<=26,[Point2226],[Age]>=27 And [Age]<=31,[Point2731]) AS Points
FROM tblPoints 
INNER JOIN (tblPerson 
            INNER JOIN tblFitnessTests 
            ON tblPerson.PersonID = tblFitnessTests.PersonID) 
ON tblPoints.Rep = tblFitnessTests.RepID;
share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.