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
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.
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.
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.