Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Question Unanswered: automatic field value in table ?

    hello, here is my question ??

    if i have two fields, field1 and field2 for example both of number type (weather integer or double)
    i want it so that after the user enteres a value into the field1 and field2, the table automaticly produces a result - a calculated value where
    field3 = field1 * field2

    nothing complex but it has to be done via the table not a query or a form, as per instructions i recieved

    any suggestions?
    thank you!
    Rudy.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, sure its not complex. Its easy.

    Until you say it can't be done via a query or a form.

    What moron gave you these instructions? Let him figure it out.

    In SQL Server, you can create a calculated field or populate the value with a trigger. No can do in Access.

    How's your resume' look these days?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    try this

    have the form pointing to a Query

    Your don't need to store field3 as this is a Cal Feild
    you alway look a the query that cal out the answer

    sql =
    Code:
    SELECT Table1.Field1, Table1.Field2, [field1]*[field2] AS field3
    FROM Table1;
    it is a rethink of the way you build tables / querys

    doing it This way field3 is allways the right answer.

    even when field1 or 2 get change the field3 will correct it self in the Printout/form
    Last edited by myle; 01-30-05 at 22:25.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Clear as mud except that you didn't read his entire post:

    "nothing complex but it has to be done via the table not a query or a form, as per instructions i recieved"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    The post was read, but was stated that in Access...no can do in Access. How do you create a Field in a Table to do calculations? You can't. You can do it in a Query and then run and UpdateQuery to place it in the Table. But you simply CAN NOT CREATE A CALCULATED FIELD IN A TABLE. Tables only Store data, but that data is manipulated in the other modules or access. Calculations are done in Query, Forms or Reports. So whoever gave you that chore to do is not knowledgeable of Access and how it works. And by the way, That really is NOT complex. Just a simple query with no programming whatsoever. If they feel it can be done then ask them to do it for you.

    have a nice one,
    BUD

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Concur it can't be done in (standard/ Plain Vanilla) Access, but it can be done if you are connecting to a back end server (of what ever variety) that allows a custom function. JET (the basic data engine for most Access applications) can't do it, but MySQL, SQL Server, Oracle etc.. can

    But why? Surely this breaks one of the golden rules about not storing derived values in a table.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, but it follows the golden rule of management that requires the loading on of unnecessary restrictions until a simple task becomes complex.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is this in nay way related to the Mushroom School of Management, "Keep 'em in the dark and chuck sh1T at 'em"?

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by rudy1582
    nothing complex but it has to be done via the table not a query or a form, as per instructions i recieved
    any suggestions? Yeah, fire the person that told you that...lol
    thank you!
    Rudy.
    Hi rudy1582,
    Quite simply put, whoever instructed you to do this at Table level knows nothing about Access. Tell whoever it is that a simple Query can do the trick,(which is by the way, what they are designed for). If they know of a way to do that at table level, then kindly ask them to show you how. Then come here and show all of us as well. And don't be confused, you're just being told to do something by someone who knows not of what they speak of.

    BUD

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And don't be confused, you're just being told to do something by someone who knows not of what they speak of.
    Those who can do, those who can't teach, or in this case (mis)manage

Posting Permissions

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