Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Automatic Derived Attribute

    Hi all,

    Is there any way to set up a field in an Access table that will automatically update from one or more fields in the same row. For instance; have a field called TOTAL get it's value from multiplying QUANTITY * PRICE?


    Thanks in advance.
    J

  2. #2
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    You should not store calculations in tables, use queries to do it.

    Put this in a query (based on the table with the Quanity and Price)

    Code:
    Total:[Quanity]*[Price]
    Regards,



    John A

  3. #3
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    This is not possible in a standard access table and isn't necessarily a great idea - it breaks one of the cardinal database normalisation rules which state that you shouldn't have a field in a table row that is calculated from one or more fields in that same row.

    However, sometimes it is necessary to break the rules when performance is adversley affected but too many calculations. You can add the following code in the data entry edit Forms BeforeUpdate Event Procedure:



    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_Form_BeforeUpdate

    With Me
    If IsNumeric(![QUANTITY]) And IsNumeric(![PRICE]) Then
    ![TOTAL] = ![QUANTITY] * ![PRICE]
    Else
    Cancel = -1 'Record Update Failed
    MsgBox "QUANTITY & PRICE required."
    End If
    End With

    Exit_Form_BeforeUpdate:
    Exit Sub

    Err_Form_BeforeUpdate:
    MsgBox Name & "_BeforeUpdate Error: " & Err.Number & ": " & Err.Description
    Resume Exit_Form_BeforeUpdate
    End Sub

    It assumes that data entry in [QUANTITY] & [PRICE] fields is required before the record is valid and can be saved.

  4. #4
    Join Date
    Nov 2004
    Posts
    3
    THANK you both
    ALthough it's a little more complex than I thought, I think I'll be using the code from MyNewFlavour.

    Access rocks!


  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would just like to say that's a really, really bad idea. Pull it when you need it, like ansentry says:

    SELECT Quantity, Price, ([Quanity]*[Price]) As Total
    FROM yourTable

    You get the exact same information except it's garaunteed accurate as of that particular moment and you eliminate the possibility of inconsistency. As an added bonus, you also experience a performance boost when inserting/updating records because you don't have to perform an additional calculation.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •