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.