Inserting Calculated Variables into a table?

Discussion in 'Microsoft Access' started by Craig, Jul 28, 2009.

  1. Craig

    Craig Guest

    Table Structure:

    Table: Siblings
    SiblingID (PK)
    ContactID (FK)
    SiblingDOB
    LastName
    FirstName
    SibSex
    SibRace
    EntryDate
    CalculatedAge
    Assessment (Is has to be calculated behind the scenes with an If/Then/
    Else statement)
    AssessmentComplete
    Blah1
    Blah2
    Blah3

    What I need to do:

    1) I need to calculate their age using SiblingDOB and EntryDate behind
    the scenes and store it in CalculatedAge.
    2) I need to take the calculate age and create groups and store it in
    Assessment. So
    a. If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
    b. If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
    c. If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
    d. Else = Error

    How do I do this in access?

    Thanks!
     
    Craig, Jul 28, 2009
    #1
    1. Advertisements

  2. Craig

    Keven Denen Guest

    On Jul 28, 11:55 am, Craig <> wrote:
    > Table Structure:
    >
    > Table: Siblings
    > SiblingID (PK)
    > ContactID (FK)
    > SiblingDOB
    > LastName
    > FirstName
    > SibSex
    > SibRace
    > EntryDate
    > CalculatedAge
    > Assessment (Is has to be calculated behind the scenes with an If/Then/
    > Else statement)
    > AssessmentComplete
    > Blah1
    > Blah2
    > Blah3
    >
    > What I need to do:
    >
    > 1)      I need to calculate their age using SiblingDOB and EntryDate behind
    > the scenes and store it in CalculatedAge.
    > 2)      I need to take the calculate age and create groups and store it in
    > Assessment.  So
    > a.      If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
    > b.      If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
    > c.      If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
    > d.      Else = Error
    >
    > How do I do this in access?
    >
    > Thanks!


    You don't. Table fields should almost never contain calculated data.
    If you need to have access to those two pieces of data, use a query to
    calculate them and use the query as the data source for anything that
    needs to interact with it.

    Keven Denen
     
    Keven Denen, Jul 28, 2009
    #2
    1. Advertisements

  3. Craig

    Keven Denen Guest

    On Jul 28, 12:03 pm, Keven Denen <> wrote:
    > On Jul 28, 11:55 am, Craig <> wrote:
    >
    >
    >
    > > Table Structure:

    >
    > > Table: Siblings
    > > SiblingID (PK)
    > > ContactID (FK)
    > > SiblingDOB
    > > LastName
    > > FirstName
    > > SibSex
    > > SibRace
    > > EntryDate
    > > CalculatedAge
    > > Assessment (Is has to be calculated behind the scenes with an If/Then/
    > > Else statement)
    > > AssessmentComplete
    > > Blah1
    > > Blah2
    > > Blah3

    >
    > > What I need to do:

    >
    > > 1)      I need to calculate their age using SiblingDOB and EntryDate behind
    > > the scenes and store it in CalculatedAge.
    > > 2)      I need to take the calculate age and create groups and store it in
    > > Assessment.  So
    > > a.      If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
    > > b.      If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
    > > c.      If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
    > > d.      Else = Error

    >
    > > How do I do this in access?

    >
    > > Thanks!

    >
    > You don't. Table fields should almost never contain calculated data.
    > If you need to have access to those two pieces of data, use a query to
    > calculate them and use the query as the data source for anything that
    > needs to interact with it.
    >
    > Keven Denen


    To calculate those in a query...

    In a new column do:
    CalculatedAge:DateDiff("yyyy", [SiblingDOB], Now())+ Int( Format(now
    (), "mmdd") < Format( [SiblingDOB], "mmdd") )

    then in another new column do:
    Assessment: IIf([CalculatedAge]>=3 And [CalculatedAge]<=5,"Test 1",IIf
    ([CalculatedAge]>=6 And [CalculatedAge]<=12,"Test 2",IIf
    ([CalculatedAge]>=13 And [CalculatedAge]<=18,"Test 3","Error")))

    Keven Denen
     
    Keven Denen, Jul 28, 2009
    #3
  4. You don't. What happens next year when the person is a year older, but the
    table hasn't been updated? What happens when someone finds a mistake in the
    SiblingDOB field and fixes it, but forgets to update the calculated fields?

    There are a few exceptions to the rule about saving calculated data, but
    this probably isn't one of them. What you want to do is calculate the age
    WHEN you need it. Same goes with the Accessment.
    --
    Jerry Whittle, Microsoft Access MVP
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Craig" wrote:

    > Table Structure:
    >
    > Table: Siblings
    > SiblingID (PK)
    > ContactID (FK)
    > SiblingDOB
    > LastName
    > FirstName
    > SibSex
    > SibRace
    > EntryDate
    > CalculatedAge
    > Assessment (Is has to be calculated behind the scenes with an If/Then/
    > Else statement)
    > AssessmentComplete
    > Blah1
    > Blah2
    > Blah3
    >
    > What I need to do:
    >
    > 1) I need to calculate their age using SiblingDOB and EntryDate behind
    > the scenes and store it in CalculatedAge.
    > 2) I need to take the calculate age and create groups and store it in
    > Assessment. So
    > a. If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
    > b. If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
    > c. If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
    > d. Else = Error
    >
    > How do I do this in access?
    >
    > Thanks!
    >
     
    Jerry Whittle, Jul 28, 2009
    #4
  5. Craig

    Craig Guest

    I completely agree with you that this isn't what you normally do,
    however, in this circumstance we want it calculated and we want it
    stored. Here's why.....

    The data being stored is for a large research study and the data will
    not change, we are only collecting this info ONCE. If you look back
    at my original email you'll notice that the calculated age is not
    based on the current date but rather the date entered (static) so the
    concept of it changing overtime is invalid. The assessment that is
    calculated is based on the static age and it will remain static. What
    you have for these two pieces of information is basically a lookup
    table so that we can calculate their age and then calculate what test
    they should take and store it. I want to use this two step process
    (calculate age -> determine assessment) to eliminate user error of
    calculating age and then figuring out which assessment they should
    take. I'm then going to use the stored data of what assessment that
    they took as a data check against the physical form they are going to
    fill out. I don't want people figuring this stuff out themselves (I
    need to make it simple and calculate it all out for them) and I want
    it stored so that it's static data. That way we can verify the data
    against data that will be scanned in from paper forms (IE does the
    form they should have gotten match what we actually received) and can
    use this information for data verification purposes for statistical
    analyses.

    Long story short......it's complicated, I get using queries and it
    would be a lot easier, but I don't think it's going to work in this
    instance. The frustrating thing is I could do this in about 2 minutes
    if I thought a query would work (maybe a triggered update since it
    will always be entered through forms?) or I could do it in SAS or
    Excel but all the info is stored in Access so I need to find a
    solution. I know I'm not the first person who's had to do this.....I
    guess I'll keep digging.
     
    Craig, Jul 28, 2009
    #5
  6. Craig

    John Spencer Guest

    So if you feel you must do this then use the Before UPdate event of the entry
    form to populate those "calculated" values using the expressions Keven Denen
    posted.

    CalculatedAge = DateDiff("yyyy", [SiblingDOB], EntryDate)+ Int(
    Format(EntryDate, "mmdd") < Format( [SiblingDOB], "mmdd") )

    and then
    Assessment = IIf([CalculatedAge]>=3 And [CalculatedAge]<=5,"Test 1",IIf
    ([CalculatedAge]>=6 And [CalculatedAge]<=12,"Test 2",IIf
    ([CalculatedAge]>=13 And [CalculatedAge]<=18,"Test 3","Error")))

    Or you can use the after update event of the two controls - SiblingDOB and
    EntryDate - to check to see if they are filled in and if they are set two
    controls on the form to the values.

    Be warned that all data entry must be through forms OR you must run an update
    query to populate the fields if you import data from other sources.

    John Spencer
    Access MVP 2002-2005, 2007-2009
    The Hilltop Institute
    University of Maryland Baltimore County

    Craig wrote:
    > I completely agree with you that this isn't what you normally do,
    > however, in this circumstance we want it calculated and we want it
    > stored. Here's why.....
    >
    > The data being stored is for a large research study and the data will
    > not change, we are only collecting this info ONCE. If you look back
    > at my original email you'll notice that the calculated age is not
    > based on the current date but rather the date entered (static) so the
    > concept of it changing overtime is invalid. The assessment that is
    > calculated is based on the static age and it will remain static. What
    > you have for these two pieces of information is basically a lookup
    > table so that we can calculate their age and then calculate what test
    > they should take and store it. I want to use this two step process
    > (calculate age -> determine assessment) to eliminate user error of
    > calculating age and then figuring out which assessment they should
    > take. I'm then going to use the stored data of what assessment that
    > they took as a data check against the physical form they are going to
    > fill out. I don't want people figuring this stuff out themselves (I
    > need to make it simple and calculate it all out for them) and I want
    > it stored so that it's static data. That way we can verify the data
    > against data that will be scanned in from paper forms (IE does the
    > form they should have gotten match what we actually received) and can
    > use this information for data verification purposes for statistical
    > analyses.
    >
    > Long story short......it's complicated, I get using queries and it
    > would be a lot easier, but I don't think it's going to work in this
    > instance. The frustrating thing is I could do this in about 2 minutes
    > if I thought a query would work (maybe a triggered update since it
    > will always be entered through forms?) or I could do it in SAS or
    > Excel but all the info is stored in Access so I need to find a
    > solution. I know I'm not the first person who's had to do this.....I
    > guess I'll keep digging.
    >
    >
    >
    >
    >
    >
     
    John Spencer, Jul 28, 2009
    #6
  7. Craig

    Craig Guest

    I had actually just started playing around subs. I tried some of the
    supplied code (see below for actual field names) and it's throwing an
    error. I'm sure it is probably something simple I am missing but if
    you happen to notice something wrong let me know. For what it's
    worth, both of those fields are date fields and both fields are
    standard access date format (mmddyy).

    *******
    Code
    *******
    Private Sub SibDOB_AfterUpdate()
    Me.CalculatedAge = DateDiff("yyyy", [Me.SibDOB], Me.DateEntered) +
    Int(Format(Me.DateEntered, "mmdd") < Format([Me.SibDOB], "mmdd"))
    End Sub

    *******
    Error
    *******
    Run time error '2465'; Database can't find the field '|' referred to
    in your expression.
     
    Craig, Jul 28, 2009
    #7
  8. Craig

    Craig Guest

    Actually, I I found a post on another forum that said to remove the
    brackets and now it works.....time for the Assessment!
     
    Craig, Jul 28, 2009
    #8
  9. Craig

    Keven Denen Guest

    On Jul 28, 2:44 pm, Craig <> wrote:
    > Actually, I I found a post on another forum that said to remove the
    > brackets and now it works.....time for the Assessment!


    Ya, I wrote it as you would if it were in the query builder, not in
    code.

    Keven Denen
     
    Keven Denen, Jul 28, 2009
    #9
  10. Craig

    John Spencer Guest

    You have brackets [] around Me.SibDOB


    Private Sub SibDOB_AfterUpdate()

    'Check to see if you have values in the two controls.
    If IsDate(me.DateEntered) and IsDate(me.SibDOB) then

    Me.CalculatedAge = DateDiff("yyyy", Me.SibDOB, Me.DateEntered) +
    Int(Format(Me.DateEntered, "mmdd") < Format(Me.SibDOB, "mmdd"))

    END IF

    End Sub


    '====================================================
    John Spencer
    Access MVP 2002-2005, 2007-2009
    The Hilltop Institute
    University of Maryland Baltimore County
    '====================================================


    Craig wrote:
    > I had actually just started playing around subs. I tried some of the
    > supplied code (see below for actual field names) and it's throwing an
    > error. I'm sure it is probably something simple I am missing but if
    > you happen to notice something wrong let me know. For what it's
    > worth, both of those fields are date fields and both fields are
    > standard access date format (mmddyy).
    >
    > *******
    > Code
    > *******
    > Private Sub SibDOB_AfterUpdate()
    > Me.CalculatedAge = DateDiff("yyyy", [Me.SibDOB], Me.DateEntered) +
    > Int(Format(Me.DateEntered, "mmdd") < Format([Me.SibDOB], "mmdd"))
    > End Sub
    >
    > *******
    > Error
    > *******
    > Run time error '2465'; Database can't find the field '|' referred to
    > in your expression.
    >
     
    John Spencer, Jul 29, 2009
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    4
    Views:
    340
    Guest
    Apr 29, 2006
  2. Paul
    Replies:
    1
    Views:
    360
  3. Geoff Cox

    inserting data into table from another table?

    Geoff Cox, Jul 12, 2008, in forum: Microsoft Access
    Replies:
    5
    Views:
    174
    Geoff Cox
    Jul 13, 2008
  4. CTJ
    Replies:
    2
    Views:
    219
  5. s
    Replies:
    0
    Views:
    580
Loading...

Share This Page