4

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.

2

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.

0

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

SELECT tblPerson.PersonName, 
    Format([DOB],"d mmm yyyy") AS [Birth Date],
    DateDiff("yyyy",[DOB],[TestDate]) + 
            ([TestDate] < DateSerial(Year([TestDate]),Month([DOB]),Day([DOB]))) 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;

The Format displays the DOB as 1 Jan 1987. The DateDiff compares the year portions of the date without regard to the month and day as mentioned in the comments. Here is what Access help says about that

"When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1, even though only a day has elapsed."

The DateSerial combines the Test year with the birth month and birth day. If the test date is earlier than the serial date, then true is -1. A false is 0. Add the -1 or 0 to the DateDiff to compute the Age.

  • 2
    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 '14 at 12:42
  • @GordThompson is correct of course. I edited the answer to compute the accurate age. Thanks for catching that. – ExcessOperatorHeadspace Jan 12 '18 at 20:11
0

The reference table you show has been crosstabulated, also known as pivoted. This is a handy representation in a spreadsheet, but makes queries awfully difficult. I have (manually) unpivoted your table to produce a relational table that looks like this:

Reps AgeMin AgeMax Points
---- ------ ------ ------
1    17     21     6     
1    22     26     7     
1    27     31     8     
2    17     21     7     
2    22     26     9     
2    27     31     9     
3    17     21     9     
3    22     26     11    
3    27     31     12    

Note that this table has nine rows instead of the three rows in your table, but only one value for points in one row. Now your query doesn't have to be dynamic.

It should be pretty easy to come up with a single query that combines the People table, the Tests table, and the Pushups table to match up the correct rows, using

BETWEEN Reference.AgeMin AND  Reference.AgeMax

as part of the match conditions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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