Software

Ask something
Discussion Locked

Calculating age between dates/Access

0 helpful
5 replies

by njanann

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My students have created a database on U.S. Presidents, and I would like for them to calculate the following:
Age of President when elected (the youngest and oldest elected)
The age of the President when he died
The fields entered are:
Date of Birth
Date of Death
Year Elected
I formatted my date/time in long date in order to calculate between centuries.

5 replies

by raffi_

Hello: You should be able to quickly calc what you need by using a couple of functions. Take a look at the MAX/MIN and DATEDIFF statements in Query Builder help. That will point your students in the right direction. For instance to get the age at election you would calc DATEDIFF between DateOfBirth and YearElected. Using this as a basis, the MAX statement will find the oldest elected.

If you want the actual SQL code I can provide that for you - let me know.

Regards

by sankulpune

Hello njanann,

I assume that you have the name of the President as one of the fields ('pName') in the table ('tblPresident') and it is defined as the primary key of the table.

You need to create two queries one to display the name of the youngest President and one for the oldest President.

1) Click 'Insert > Query'.
This will open the 'New Query' dialog box.
2) Select 'Design View' and click 'OK'.
3) This will open the 'Show Table' dialog box. Select the table 'tblPresidents', click'Add' and then 'Close'.
This will close the 'Show Table' dialog box.
4) In the list of fields displayed in the top pane of the Query Design grid (titled 'Query1 : Select Query') double click on 'pName'.
This will add the field in the first columnof the query.
5) In the second column, type the following in the field row:
= DateDiff("yyyy", BirthDate, ElectDate)
The formula will change to Expr1 = DateDiff("yyyy", [BirthDate], [ElectDate])
You can overwrite Expr1 with a variable name (eg 'ElectAge') of your choice. The field name will thus look:
ElectAge = DateDiff("yyyy", [BirthDate], [ElectDate])
6) In the 'Sort' row of the ElectAge field, select 'Descending'.
7) Click in a blank area in the query design (ensuring no field is selected) and click the menu View > Properties.
This will display the 'Query Properties' dialog box.
Set the 'Top values' property to 1.
8) Close saving the query giving the name "Oldest".

When you run this query, it will display the name and age (in years) of the President who was elected at a an age greater than any other President.

9) To find out the youngest President when elected, copy the above query with the name "Youngest" and change the 'Sort' row value of the 'ElectAge' field to 'Ascending'.

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

This discussion is closed to new replies.