representation in ms-access

by Ritesh Seth   Last Updated January 12, 2018 20:06 PM

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.



Answers 2


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.

Henry Helgen
Henry Helgen
July 16, 2013 22:01 PM

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.

fishingaddict
fishingaddict
December 05, 2015 04:35 AM

Related Questions


Forms with combo box

Updated February 01, 2017 14:02 PM

How use the IsNull function and IsEmpty in Ms Acces

Updated August 06, 2018 10:06 AM

Distinguishing Records with Related Fields

Updated July 26, 2015 13:02 PM