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

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.

share|improve this question

1 Answer 1

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, 
       tblPerson.DOB, 
       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
    
It is important to note that DateDiff("yyyy",Date1,Date2) does NOT return the number of full years between the two dates; it is the equivalent of Year(Date2)-Year(Date1). To illustrate: DateDiff("d",#2013-12-31#,#2014-01-02#) returns 2 because the dates are 2 days apart. However, DateDiff("yyyy",#2013-12-31#,#2014-01-02#) returns 1. In this case using just DateDiff("yyyy",...) would tell you that a 2-day-old baby was one year old. –  Gord Thompson Nov 24 at 12:42

Your Answer

 
discard

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.