access caluclated field query

Hi,

I am entering height, weights and age into a database and I want to
create a query which enables Access to automatically caluclate BMI
(two kinds depending upon the age of the kid)

Any suggestions on how to do this?

Thanks!
E

0
airnuminz
7/10/2007 4:01:40 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
913 Views

Similar Articles

[PageSpeed] 47

On Tue, 10 Jul 2007 09:01:40 -0700, airnuminz@yahoo.com wrote:

>Hi,
>
>I am entering height, weights and age into a database and I want to
>create a query which enables Access to automatically caluclate BMI
>(two kinds depending upon the age of the kid)
>
>Any suggestions on how to do this?
>
>Thanks!
>E

BMI - and for that matter age - should NOT be stored in ANY table; they're
derived data which can and should be calculated. Rather than storing the age,
store the birthdate; create a Query based on the table and in a vacant Field
cell type

BMI: [Weight] /([Height]*[Height])

assuming weight in kg and height in meters. If you have a different
calculation, use it! Similarly, use an expression to calculate the age:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd", 1, 0)


             John W. Vinson [MVP]
0
John
7/10/2007 4:57:34 PM
On Jul 10, 12:57 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Tue, 10 Jul 2007 09:01:40 -0700, airnum...@yahoo.com wrote:
> >Hi,
>
> >I am entering height, weights and age into a database and I want to
> >create a query which enables Access to automatically caluclate BMI
> >(two kinds depending upon the age of the kid)
>
> >Any suggestions on how to do this?
>
> >Thanks!
> >E
>
> BMI - and for that matter age - should NOT be stored in ANY table; they're
> derived data which can and should be calculated. Rather than storing the age,
> store the birthdate; create a Query based on the table and in a vacant Field
> cell type
>
> BMI: [Weight] /([Height]*[Height])
>
> assuming weight in kg and height in meters. If you have a different
> calculation, use it! Similarly, use an expression to calculate the age:
>
> Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
> Format(Date(), "mmdd", 1, 0)
>
>              John W. Vinson [MVP]

THANKS!

0
airnuminz
7/10/2007 5:15:14 PM
Reply:

Similar Artilces:

Field names in an append query
I am attempting to append one table with another. I have several fields whose names are in the form of xxx - yyyy, aaaa - bb, etc. There is a space on either side of the hyphen. One of the fields, named "name - last" (no quotes) is not being recognized, for a reason I can not understand. Therefore the query will not run. I have tripled checked that the names are in fact exactly duplicated in each table and that the properties are likewise duplicated. I am at loss to determine what the problem might be????? Thanks Paul Are you doing this from the QBE grid in design m...

Windows Terminal Server 2008 / non-clients need to access internet
I have a terminal server environment. I have access points all over the school. I would like visitors to be able to access the internet but in order to get on you have to open a session. How do I get it shared to non-client pcs? -- rubantin ------------------------------------------------------------------------ rubantin's Profile: http://forums.techarena.in/members/190718.htm View this thread: http://forums.techarena.in/windows-server-help/1311934.htm http://forums.techarena.in Hello rubantin, "How do I get it shared to non-client pcs?" Do you mean with ...

Error when launching Access Forms
1) I do not understand what's wrong with my forms which always have this error message: "Your Microsoft Office Access Database or project contains a missing or broken reference to the file 'LDDateTm.ocx' version 1.0". I've checked the links in form that uses a macro to control a button action. The macro is supposed to OpenForm(), GoToNewRecord() and Close(). 2) The next error is: "The object doesn't contain the Automation object 'Hugo'. You tried to run a Visual Basic procedure to set a property or method for an object. However, the component d...

access co. email
to get my company email mess. (outlook) im suppose to go www.//.?..mailexchange..? my co. name. com and then i can put in my screenname to acess..please advise how to access? rhino99 wrote: > to get my company email mess. (outlook) im suppose to go > www.//.?..mailexchange..? my co. name. com and then i can put in my > screenname to acess..please advise how to access? Nobody but your company's IT staff can help you with this - ask them for the correct address to use in your browser. Outlook Web Access is part of Exchange, not Outlook, by the way....good luck! ...

Format Currency field into zero filled text field
I have a field that can be blank or contain a currency amount. No matter what the value is I want it to be a zero filled field. So for $9.99 I want 0000999 for a blank field i want 0000000 if it is $990.00 I want 0099000. It seems I can get parts to work. Format(Salary, "0000000") returns the amount rounded so I lose cents if I use Format(Salary, "00000.00") i get it to work but have to manually remove space. The other problem I am having is having it zero fill when no value Thanks, Marcie Try: Format(Salary * 100, "0000000") -- Duane...

Smartlist builder calculated fields
Hello, I am trying to do a datediff function in smartlist builder, and I was wondering if that is possible. Here is the code I have in the calculated field-- {*mdelotnumberinfo:Date MFG} + (datediff(d, {*mdelotnumberinfo:Date MFG} , {*mdelotnumberinfo:Date EXP} )*.5 And here is the original SQL data query that does work-- select mfgdate + (datediff(d, mfgdate, expndate)*.5) as 'Half Shelf Life' from iv00300 where mfgdate != '1900-01-01' and expndate != '1900-01-01' order by lotnumbr Any thoughts? ...

outlook web access #38
When I click on the address book icon in Outlook for Web 2003 it i empty. However if I type in a name on the search function th information on the individual appears from the global address book. Ho can I get the global address book to show all entries when it is evoke from outlook for web as it does for outlook 2003 locally. Also I ca only see the last 25 emails when I check email from outlook for web. I there any way to display all emails in the in-box. I appreciate an help I canget ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and po...

Case When statement in Access Frontend with Sql Backend
I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. [code]'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THE...

Advanced Find Search on Bit Field
Hello- I am trying to search on a bit field within CRM 3.0 and its not an option in the drop down on the advanced find form. Does anyone know how to search on a bit field? i.e. does not allow bulk email? This is important for us because we wanted to pull all of our accounts that are Approved and I have it set up as a bit field. Hi Tiffani, You should be able to use bit fields in advanced find. In the list there is "Do Not Allow E-mail" as well as "Do Not Allow Fax" and "Do Not Allow Phonecalls. Hope this helps, -- Ronald Lemmen - MSCRM MVP Avanade Netherlan...

Fixed Asset IM import ADO Field is Nothing message
I've setup a new Integration Manager routine (v 10) to Fixed Assets Book. When running the integration the error log states 'ERROR: ADO Field is Nothing'. First, I have successfully used IM to integrate Fixed Assets General Information, so I know that it works with my implementation. DTC is setup and the eConnect IM worked then. It is hooking into an Excel spreadsheet (xls version). I can see the source information (column definitions). I've got the database connection setup. Knowledge base comes up with no articles. Thanks in advance! Hi DD I've had that...

Access to Outlook using public IP
I was trying to secure our mail server when I encountered a problem in accessing my email. We have a domain controller SERVER A, 2 exchange 2003 mail servers SERVER B and C. SERVER A has no public IP while SERVER B and C are connected to the internet. My problem is, I was able to access my email using microsft outlook when I used 1 of our public IPs. Simply put, it authenticates with our Active Directory. The first problem you have is that your internal servers are assigned public IP's. There is no reason whatsoever for your internal servers to have public IP's. All of...

Need a report or query(?)
I have a table of class information that contains class name, start date, instructor, start time and days held - the days held (if more than one day) repeat in separate records. I need to end up with one record per class that shows class name, instructor, start date and the days and times held. (Also in the mix is that times held can be different each day held.) Where do I start? A query? Two queries? I started by creating a table and grouping on class name so I have a table with non-dup class names...now, I am dead in the water! Thanks in advance! On Tue, 18 May 2010 0...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

Query Q
Im tring to categorise some records based on a rule which says; we have a 2 hour grace period, then every thing is categorised different. we have a bunch of duration values, ie 2hr, .25hr, 1 hr, whatever. If the event is over 2 hours- then it is category 2. if we have 1.5hr first, then we have .5hr left of grace- anything longer will be cat2. ie, this is what i want the query to return.... ..5hr -grace period (first .5hr grace used) 3hrs - cat2 1hr -grace period (now total grace is 1.5hrs) 2hrs -cat2 1hr - cat2 (because now we have only .5hr grace left) ..5hr -grace (now all 2hrs grace is ...

too long execution time and access violation error
Hello, Here is my problem. I first wrote a program for simplicity as a console application for image processing tasks. The program runs fine and gives correct result(a processed image) with an execution time of 30 microseconds. It has to run as MFC application. So i added few controls and a function to MFC and just copied my conole app code inside that new function. What happens now is that the same code that took 30 microsecond in console is now taking 55 seconds in MFC although in the end the correctly processed image is displayed. But then when i exit the application it raises access vio...

List box (and queries) not sorting dates or showing correct headers
G'day all, I have inherited a system with a search function screen that is acting weird. There are actually two search screens, both constructed in a similar fashion, but the first does what it is supposed to do and the second is misbehaving. The form has a bunch of unbound controls allowing the user to enter search criteria. The only mandatory criteria is the sort order, which is set from a combo box. The data source for the combo box is a table, sys_GAPSSortBy, which contains a list of query names and a user- friendly label for use by the combo box. When the user clicks the "Searc...

How do I change the ruler to inches in access
It's generally considered polite to put your question in the body of the post, rather than strictly post a subject. As well, details such as what version of Access and of Windows can be useful. That having been said, Access gets its unit of measure from the operating system. The only way to have the ruler in Access be in inches is to choose U.S. rather than Metric for the Measurement Units in Regional Settings in the Control Panel. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Johnotaker" <Johnotaker@discussions.microsoft...

Synchronization of "Company" field in Outlook Contacts
Hi , We have a problem where the "Company" field in the Outlook Contacts is not getting populated with the "Company" field in the MSCRM Contacts when we synchronize MSCRM Outlook client with MSCRM. Any one who has worked on this or faced the same problem please getback. Thanks in Advance Kiran This has been suggested to Microsoft to add this feature. If you'd like to "vote", search for threads with suggestions for MS, and search on the words Company Outlook It's not available at this time. --Karla "KIRAN" wrote: > Hi , > We h...

Error when trying to access Sales Transaction Entry
Hi, When trying to access Sales Transaction Entry I get following error "You don't have access to any of the document types". I am new to GP and I just can't figure this out. I have setup 5 users and I get same the error on all five. On one of the user I gave all the rights and still getting the same error. However, for my user id, this windows opens perfectly fine. Of course, I have copied just about same rights as sa. Any ideas on what I am doing wrong? -- Paresh What version of GP you have will determine the solution. It's all through security. -- ...

Access 2003 databases keep corrupting
I am using a new laptop (six months old) and am the first person to use Access 2003 on it. I copied two databases from other machines to it and created one from scratch. All of these databases keep crashing, but sending the error message to MS doesn't result in a response from them. These databases have no problems on other machines. e.g. I wanted to use a form as the basis for another one, so used CTRL C CTRL V to copy it, giving it a new name. The new form did not appear, but when I closed the database it asked if I wanted to save it, and a module of the same name. I said no...

Retrieve Full Path From Attachment Recordset Multi Value Field
Hello, Is it possible to retrieve the full path from the Attachment Multi Value Field in Access 2007. It seems that Access obviously stores it if it knows where to find it, but is the full path available. If it is not, is it possible to expose the path when you select the attachment on a form? Thanks Richard ...

data in queries
Hi! From a dataset I retrieve customer names and the products they have purchased: client name product name client 1 product a client 1 product b client 1 product c client 2 product b client 2 product d client 3 product a client 3 product e No problem. But how can I avoid the repetitions in the ClientName column: client name product name client 1 product a product b product c client 2 product b product d client 3 product a product e In Query View. I think in a Report this is done through Group By? Thanks ...

Outlook 2003 thesaurus tries to access office.microsoft.com
When using the Thesaurus within Outlook 2003, Outlook tries to access the Internet site office.microsoft.com. This only happens the first time the Thesaurus is used following a reboot of the PC. Thanks, Dennis Does anyone know why it does this? Thanks >-----Original Message----- >When using the Thesaurus within Outlook 2003, Outlook >tries to access the Internet site office.microsoft.com. >This only happens the first time the Thesaurus is used >following a reboot of the PC. > >Thanks, >Dennis >. > ...

Question about backups in Access 2007
I am using Access 2007 with the options set to save the file in 2000-2003 format. The database is split with the backend in a folder on a server. When I backup the database, a backup is copied to my backup folder, however access is now putting a copy of the backend database in the folder with the backend file. What is this copy used for and can I delete them? Any and all help greatly appreciated. Emily ...

Problem with Access merging into Word
Hello All, I need some assistance. First a little background on the database: The back end is in AQL the front end is in Access. The main form is called the "Register", here they enter claim information and if they need to create a printed loss they click on a button called "Loss Notice" This brings up another form which is filtered based on the claim number in the Register. Here they fill in additional information. On this form they have the option to print the Loss Notices by clicking on a button that Opens up Microsoft Word and all the fields are mapped to th...