Results 1 to 10 of 10
  1. #1
    puush is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    NorCal
    Posts
    4

    BMI calculation

    Hello,



    I have a form to keep track of diabetes patients. I need to calculate the BMI (body mass index) for patients. The body mass index calculation is BMI = weight *703 / height "inches".

    I have a "BMI" Field a "Weight" Field and a "Height" Field in my form. When a user puts a patients weight and height in their fields I want the values to stay as they are input in field. But I want the BMI calculation for a patient to show up in the BMI field. How do I do this? Thanks


    Michael

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    In general, calculated values are not stored in a table, but rather calculated on the fly when you need them. So the short answer is that you would not store the BMI, just calculate & display it when you need it. You would, however, store the raw numbers that are used in the calculation as you mention. On your form, you would add a textbox control and set its control source to the equation you provided and reference the height and weight controls



    Are there other parameters in addition to height and weight that you are interested in capturing (but not necessarily used in the BMI calculation)?

    What does your table structure look like at this point? I assume that you have a table of patients which is related to a table that captures when they visit (and have the height and weight recorded), correct?

  3. #3
    puush is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    NorCal
    Posts
    4

    Thanks- expression builder help

    Yes. I have fields in a form to keep track of patient data as they go thru a diabetes examination. I went to design view on my form and selected the "BMI" field and went to "control source" and wrote this expression:

    =[PatientBMI]=[PatientWeight]*703/[PatientHeight]

    The "PatientHeight" value needs to be (squared). How do I write the squared value on "PatientHeight" field in expression builder?

    Hence, if I do this, when I enter PatientWeight and PatientHeight values in thier fields the BMI value should calculate in its field correct? I provided a snapshot of form in design view and the expression I wrote in expression builder. Thanks for your help.


    Michael A

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The control source should be this:

    =[PatientWeight]*703/[PatientHeight]^2

    I saw in your pdf that you only have 1 table. If you have patients that come in multiple times for the same type of examination, the one table design will cause problems. In addition to weight and height, what other parameters of the patient do you collect at each exam? Do you look for trends in these data as well as the BMI?

    Hence, if I do this, when I enter PatientWeight and PatientHeight values in thier fields the BMI value should calculate in its field correct?
    It will calculate in the control, but as I mentioned previously, the calculated value should not be stored in the underlying table. It is not good database practice and could lead to data integrity issues.

  5. #5
    puush is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    NorCal
    Posts
    4

    think its working

    I put the formula in the expression builder for the BMI field and tested it. it seems to work. I have to verify results with the old calculator. I think currently we are going to enter patient data only once. I have currently only one table and the primary key is currently set to an ID field to prevent any duplication. The director who is managing this project will probably want to build on this database and add more fields that are also calculated I believe. We Are doing heart exam stuff also so we will add 40 more fields to this form or create a new database. Not sure yet.

    I believe she wants to do do statistical analysis after gathering some patient data.

    I attached snapshot of simple table I have. We will use this simple form at a health fair we are doing march 24th. Then go from there. Thanks for all your help man.

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Technically speaking, the database as you currently have it designed will not do well if the expansion you mention goes forward, and it is marginal at present. I say that because the design is not normalized. For more info on normalization, please consult this site.

    We Are doing heart exam stuff also so we will add 40 more fields to this form or create a new database
    40 fields in a table can be a problem if they are actually specific items (just like your present height and weight fields). What would happen if you wanted to add 5 more fields (additional tests)? You would have to redesign the table and all related forms, queries, reports etc. That would not be fun to do each time new tests are added.

    What I would do is have a table that holds the parameters/tests (height/weight) that you will be using as part of your exam of patients and relate those to the patients. And if the intent is to collect statistical data, then you will probably have to monitor a patient's results for each test/parameter over time, which requires you to capture each patient's visit or time when those tests are performed. Since, your requirements for this future database are not fully developed, I won't go into a lot of detail, but as a basic structure, I would start off with something along these lines:

    tblPatients (holds the basic patient information)
    -pkPatientID primary key, autonumber
    -txtFName
    -txtLName


    tblParameters ( a table that holds the things you can measure: height, weight, BP, glucose etc.)
    -pkParameterID primary key, autonumber
    -txtParameterName

    A patient can have many visits (one-to-many relationship)

    tblPatientVisits (holds the information for each visit of a patient)
    -pkPatientVisitID primary key, autonumber
    -fkPatientID foreign key relating to tblPatients
    -dteVisit (date of visit)


    Many parameters can be measured at each visit (one-to-many relationship). A parameter can be measured at many visits (one-to-many). These two one-to-many relationships between the visits and parameters require a junction table in order to relate them properly. The following table is that junction table

    tblPatientVisitParameters (holds the parameters that are to be measure during a particular visit by the patient)
    -pkPatientVParameterID primary key, autonumber
    -fkPatientVisitID foreign key to tblPatientVisits
    -fkParameterID
    -ParameterResult (the actual height, weight, BP etc.)

  7. #7
    puush is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    NorCal
    Posts
    4

    thanks

    Thanks for the information. I should maybe create more than one form also? This is getting a little complicated but I will give it a try. The Manager overseeing this project did say that she would just like to gather information raw and then later pull it out for analysis but then she threw the BMI formula in there. i have basic knowledge of database relationships.

    I have attached the form that the manager wants to make into access 2010 database.


    Michael A

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would generally use more than one form and navigate the user through them.

    In terms of the pdf you attached, that would be difficult to do using bound forms in Access because the data needs to be distributed into the correct tables. I would probably use an unbound form with Visual Basic for Application code to put the data into the correct tables. That will take some effort and some knowledge of code.

  9. #9
    klynn is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2014
    Posts
    1

    Suggestions welcomed

    I stumbled upon this forum and found it useful for the database I am creating for an Orthopedic Surgeon. I would like to pick your brain 'jzwp11' about some concerns I have with the database.

    The purpose of the database is for the surgeon to pull statistics and research data for future papers or projects.

    All of his patients are entered in by a PPRN (patient number) followed by Age, Height, Weight and BMI. THEN I need to create drop down fields that identify the diagnosis, operation and complication the patient was seen for, which needs to be all in the same table.

    Does this make sense to have all this information all in one table?

    Thanks for you suggestions

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Typically the values that populate dropdowns are stored in tables, so you will have a table that contains all possible diagnoses, another for operations. If you can catalog a list of possible complications, then those too would be in a table. Are you going to be capturing the height, weight and BMI at various times or only once? If more than once, that indicates a one-to-many relationship which by normalization rules requires that data to be in a separate but related table (i.e. related to the patient). As to age, you only capture the date of birth and calculate the age as necessary, there is no reason to store the age time and time again.

    You will also have to keep in mind that you can have multiple operations per 1 diagnosis (one-to-many relationship) as well as multiple complications for one operation (another one-to-many relationship).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculation Help
    By edge5511 in forum Programming
    Replies: 4
    Last Post: 12-18-2015, 11:11 AM
  2. Need help with calculation please
    By Gabriel984 in forum Forms
    Replies: 6
    Last Post: 09-09-2010, 12:06 PM
  3. Calculation Help
    By ErnieS in forum Access
    Replies: 13
    Last Post: 07-08-2010, 08:35 AM
  4. Date Calculation -- HELP!
    By klaauser in forum Forms
    Replies: 0
    Last Post: 12-22-2008, 02:14 PM
  5. Calculation
    By thestclair in forum Reports
    Replies: 1
    Last Post: 04-29-2006, 11:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums