[Solved] How to calculate someone's age in a report

Discuss the database features

[Solved] How to calculate someone's age in a report

Postby aplucini » Fri Dec 23, 2011 6:53 am

There must be an easy way to do this!
In order to learn how to calculate someone's age based on today's date and a birth date, I've created a very simple table with fields NAME, BIRTHDATE, and AGE
The BIRTHDATE field is of type DATE of course
AGE is of type DECIMAL. I'm not sure whether I really need to create a field AGE since I only want to display a calculated age in years in a report not store the age.

I'm trying to create a report using Report Builder.
In Access 2007 I simply create a calculated field in the report. Inside that field I type the formula (Date()-[BIRTHDATE])/365. Date() is today's date. The report will then generate an age in years such as 45.7.

In open office Base there doesn't seem to be a way to create a calculated field in the report. I tried inserting the above formula in the AGE field in the report but the report just returns an age of 0. That formula doesn't seem to work in ooBase.

Eventually I'd like to be able to perform other complex calculations in various databases or tables but right now just figuring out this simple age calculation would be a good start.
Last edited by TheGurkha on Tue Dec 27, 2011 1:20 am, edited 2 times in total.
Reason: Tagged Solved, TheGurkha.
Open Office 3.3, Windows 7
aplucini
 
Posts: 14
Joined: Sun Dec 18, 2011 1:38 am

Re: How to calculate someone's age in a report

Postby F3K Total » Fri Dec 23, 2011 6:13 pm

Hi,
take a query with this statement to have the exact todays age.
Code: Select all   Expand viewCollapse view
SELECT "NAME", CASEWHEN( MONTH( "BIRTHDATE" ) * 100 + DAYOFMONTH( "BIRTHDATE" ) > MONTH( CURDATE( ) ) * 100 + DAYOFMONTH( CURDATE( ) ), DATEDIFF( 'year', "BIRTHDATE", CURDATE( ) ) - 1, DATEDIFF( 'year', "BIRTHDATE", CURDATE( ) ) ) AS "AGE" FROM "MYTABLE"

You can take the query as input for your report.
greats R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo 3.3.0, AOO 3.4.1, 4.0.1, LO 4.0.6, 4.1.5 Linux Mint AOO 3.4.1, 4.0.1, LO 4.0.6, 4.1.5
F3K Total
Volunteer
 
Posts: 404
Joined: Fri Dec 16, 2011 8:20 pm

Re: How to calculate someone's age in a report

Postby aplucini » Fri Dec 23, 2011 6:59 pm

Thanks for your response. That SQL query correctly calculated their age and the query works great for this simple table with it's simple data. However, a SQL query will be much more complex for a more complicated database with many additional fields and the requirement for more complex queries and reports. I don't know SQL worth beans and I don't know how I would incorporate this SQL query for calculating age into a much more complex query for a more complex database and table.

It seems to me that there must be an easier more user-friendly way (i.e. in Access 2007) to generate the age in a report.
Open Office 3.3, Windows 7
aplucini
 
Posts: 14
Joined: Sun Dec 18, 2011 1:38 am

Re: How to calculate someone's age in a report

Postby Sliderule » Fri Dec 23, 2011 10:43 pm

You said:

In Access 2007 I simply create a calculated field in the report. Inside that field I type the formula (Date()-[BIRTHDATE])/365. Date() is today's date. The report will then generate an age in years such as 45.7.


Therefore, if TODAY is 2011-12-23, and BIRTHDATE is 1992-12-31 . . . what would it return?

And, is 19 an 'acceptable' answer, since, you are dividing by 365 and ignoring LEAP years ? Duh.

For the built-in HSQL functions:

  1. CURRENT_DATE will return the date of that the database is being run on
  2. DATEDIFF using the first parameter of 'dd' will return the number of days between the second parameter and the third. For example:

    Code: Select all   Expand viewCollapse view
    DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE)

  3. If you want to divide the number of days by 365, and, return it with a precision of 1 decimal place, you could divide it by 365.0 and if you want to return it by a float number, with a precision of 14 decimal places, you could define it as 365E0 ( 365 followed by the letter E followed by the number 0 . . . all withOUT any spaces )

  4. For example:

    Code: Select all   Expand viewCollapse view
    -- Return # days between two dates divided by 365.0, precision of 1 decimal place
    DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE) / 365.0

    -- Return # days between two dates divided by 365E0, precision of 14 decimal places
    DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE) / 365E0

  5. Therefore, in your Query, you could write it as:

    Code: Select all   Expand viewCollapse view
    Select
       "MyTable".*
       DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE) / 365.0 as "Age 1 Decimal",
       DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE) / 365E0 as "Age 14 Decimals"
    From "MyTable"

  6. If you are using the GUI ( Graphical User Interface ) Create Query in Design View... to create your query, on the Field line, enter EITHER ( or both, your choice ):

    Code: Select all   Expand viewCollapse view
    DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE) / 365.0

    DATEDIFF('dd', "BIRTHDATE", CURRENT_DATE) / 365E0


    and, use the Alias line to assign your desired output column heading
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
 
Posts: 660
Joined: Thu Nov 29, 2007 9:46 am

Re: How to calculate someone's age in a report

Postby aplucini » Sat Dec 24, 2011 2:34 am

Thanks sliderule. I will take a close look at your suggestions. At this point it is still over my head but maybe I can figure it out. I don't know how you work this in to a report design in Report Builder. I would certainly be using the GUI since I don't know SQL.

I'm not too concerned about ignoring leap years in any calculations. The resulting degree of inacuracy would be insignificant for my purposes where I am printing a roster of people in an organization where I want to print their current age next to their birth date.
Open Office 3.3, Windows 7
aplucini
 
Posts: 14
Joined: Sun Dec 18, 2011 1:38 am

Re: How to calculate someone's age in a report

Postby aplucini » Mon Dec 26, 2011 11:24 pm

I got myself a good book that has taught me a lot about using SQL and also found some info somewhere on the internet (I can't remember where!).
I downloaded and installed MYSQL on my PC and have learned a lot about SQL in a short time. I'm still a novice but nevertheless I've increased my SQL knowledge dramatically. I tested an age calculation on a test table at the MYSQL command line prompt and then did the same simple calculation in the open office SQL view screen where it also worked. YEAR( CURDATE( ) ) - YEAR( "DOB" ). I then opened the same query in the Open OFfice Create Query Wizard and saw where the sql command is placed in the wizard. I then simply added all the fields I wanted displayed in the query (and subsequent report based on the query) and the report came out perfect including a column for each person's age.

One thing I've noticed though is that SQL commands I run in mysql sometimes don't work in Open Office environment. I've also noticed that if I create a table in the Open Office SQL View window, the table gets created OK but I am unable to enter data into the table. It seems to be a read-only table. I can't figure out how to make it writable. But there is no real reason to create the table in SQL View since it is much easier using the GUI of Open Office anyway.
Open Office 3.3, Windows 7
aplucini
 
Posts: 14
Joined: Sun Dec 18, 2011 1:38 am

Re: [SOLVED] How to calculate someone's age in a report

Postby Villeroy » Mon Dec 26, 2011 11:43 pm

aplucini wrote:I got myself a good book that has taught me a lot about using SQL and also found some info somewhere on the internet (I can't remember where!).
I downloaded and installed MYSQL on my PC and have learned a lot about SQL in a short time.

Excellent!
aplucini wrote:One thing I've noticed though is that SQL commands I run in mysql sometimes don't work in Open Office environment.

These failing commands might be SELECT queries. You can run them in "direct SQL" mode. Turn off the graphical designer and turn on menu:Edit>Run SQL directly. This passes the whole interpretation job over to the underlying database.
[Tutorial] Structured Query Language in Base

aplucini wrote: I've also noticed that if I create a table in the Open Office SQL View window, the table gets created OK but I am unable to enter data into the table. It seems to be a read-only table.

Because Base is so simplistic and not specific to a particular database engine, it has to be very picky about writing record sets in order to do this safely.
Your database table needs to have a primary key and the row set must be derived from a single table, include the table's primary key and must not be run in direct SQL mode.
[url=http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=26448[/url]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17316
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests