Calculated Field updated to table

Creating tables and queries

Calculated Field updated to table

Postby bwhitman » Tue May 16, 2017 11:52 pm

I see this question has been asked and answered, but not for a while, so maybe something has changed.

I have built a simple query to calculate current age based on DOB (date of birth) and CURRENT_DATE. Is there a way to take the results of that query and update a Current Age column in my main table. I understand that that is not really the way it is meant to be used, but I have a user who is tracking patients in an Excel Spreadsheet and I am switching her over to Base. She currently goes into each record on a periodic basis and manually updates the person's age. :knock: I would like that to be something she never has to do again. The powers that be have told her that the field needs to exist, so until I can get some change there, I would like to try and automate that process somewhat.

Thanks,
Benedikte
OpenOffice 4.1.1
Windows 7
bwhitman
 
Posts: 4
Joined: Mon Apr 03, 2017 11:51 pm

Re: Calculated Field updated to table

Postby Arineckaig » Wed May 17, 2017 12:02 pm

I have a user who is tracking patients in an Excel Spreadsheet and I am switching her over to Base. She currently goes into each record on a periodic basis and manually updates the person's age.

Calc can be used as a stand alone spreadsheet linked to the output of a query stored in a registered Base file. Each time the spreadsheet is opened it can be set so that its content is automatically updated from the query containing the required data including any calculated field(s). The Calc file can then be saved as an Excel spreadsheet.

See Villeroy's tutorial at https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=40493 for pointers on using Calc to show report output from linked Base file(s). Base may have many limitations but its great strength is its ability to work with other elements of AOO: in particular, the use of Writer and Calc for complex bespoke database reports.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Calculated Field updated to table

Postby Villeroy » Wed May 17, 2017 12:13 pm

[Tutorial] Using registered datasources in Calc explains how to link cell ranges to database queries.
https://forum.openoffice.org/en/forum/v ... 10#p416210 provides a pair of demo documents demonstrating input forms on sheets and one linked cell range.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25956
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated Field updated to table

Postby Villeroy » Wed May 17, 2017 12:54 pm

By the way: there is no reason to store the DOB and the age in 2 independent fields. It makes no sense. It's wrong.

SELECT "table".*, YEAR(CURRENT_DATE)-YEAR("DOB")+CASEWHEN(DAYOFYEAR(CURRENT_DATE)>DAYOFYEAR("DOB"),1,0)AS "Age" FROM "table"
selects all fields from "table" and appends a field "Age" calculated from a field "DOB"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25956
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated Field updated to table

Postby Sliderule » Wed May 17, 2017 4:55 pm

Please see the link below, about creating a calculated AGE field in a Query, or View. The link discusses why you do not ever want to 'store' an AGE column in your table, but rather, have it as a calculated column from a Query, and/or a View. :mrgreen:

https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=72365&p=325606&hilit=age+calculate+query+view#p325581

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
Volunteer
 
Posts: 1153
Joined: Thu Nov 29, 2007 9:46 am


Return to Tables & Queries

Who is online

Users browsing this forum: msahines and 2 guests