Project 3B Overview | Sailor's Age | Query in Query | Structure Modifications | Export XML

next page

Modifications - sailor's age

 

Originally, we had a field where we stored the sailor’s age. There were a couple things wrong with that. First, the data type was currency to allow us to use two decimal places. Second, every time someone had a birthday, we had to go back and update the database. In this version of the database, we've removed the age field and replaced it with a birthdate field. Now we can always get the correct age by calculating it. In fact, we’ve created a query to do that for us–querySailorAge.

1. To see querySailorAge, click on the drop-down button next to the Tables heading and select Queries:

 

You'll see querySailorAge in the left-hand column. Double-click it to open it:

2. Look at the query in design view . (In the screenshot below, the current age column has been widened):

 

You can see that only one table, tableSailors is used in this query. The first two columns (ID & birthdate) are fields in tableSailors.

But the third column (current age) doesn't exist in the table—it is calcuated. Here's an explanation:

The Field row contains this:

current age: DateDiff("yyyy",[tableSailors]![birthdate],Now())

The first part, current age, is the name of the newly created column. (The image below shows what you see if you run this query - note that current age appears as the title):

The second part, DateDiff, is a function (remember functions in Javascript?)

Just like a Javascript function, the function is written with the name of the function followed by parentheses:

DateDiff()

What does DateDiff() do? It tells you how far apart two dates are (i.e. it is the difference between the dates).

Three things go inside the parentheses after DateDiff:

1. How you want the results to appear—as number of days? number of months? In the screenshot, we chose number of years, represented by "yyyy" while in the database you downloaded, we chose "d" for days.
2. and 3. The dates we want to find the difference between:

[tableSailors]![birthdate] refers to the field birthdate in tableSailors

Now() is a function that always brings up the current day's date

So DateDiff("yyyy",[tableSailors]![birthdate],Now()) calculates the difference, in years, between today and this person's birthdate—just the way you calculate a friend's age or how long you've been together if it's your anniversary.

3. Change "d" to "m" , then view the results. You'll see each person's age expressed as the number of months.

4. Go to the Catalyst quiz and answer question 1.

Next, you'll use this query to build another query...