SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dynamic field in access?

    I am working on a small database in ms_access, and I was wondering if there is a way to create a dynamic value into a field, EG. update another table but the first table will update a field, that lets say, is a sum of all the values on the second.

    If anyone is lost by my example please yell at me

    - Michael Dawson

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you want to do is create a trigger; when a field is updated, something else should happen too
    Don't think you can create triggers in Access

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But of course you can do it manually in code - assuming that the data is only ever updated via a form, then you can use VBA to update whatever information you want.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But then again; you have a small db, ms access, and you want a field containing e.g. the sum of values from some table?...
    (Typical example; an Order, and you calculate the sum of Price * Amount for all Order Items in that Order)

    You could solve this "problem" using a calculated control in a form/report
    After all, you have the values stored in the db already, you don't need another column in another table, that's redundant info, and the only good reason to store duplicate data like that is that you want better performance (don't waste time calculating that sum or whatever) - but I don't think that argument is valid in an ms access db with only one user

  5. #5
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    update another table but the first table will update a field, that lets say, is a sum of all the values on the second.
    I assumed that the sum was merely an example, not the actual desired calculation. But as you say, for simple calculations like that in a single-user environment, an on-the-fly query would be best.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  6. #6
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe, "I assumed that the sum was merely an example" dont know about what jofa would say, but in my case its exactly what i would like to do. its a financial database, I want to add all the monthly transactions (positine and negative)

    I'll probably just end up updating a normal field every time

  7. #7
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's fine - I was merely misled by your "let's say"...

    As Jofa says, all you need is a simple query to obtain that information, or even better, you could alter the form's record source to be a query which contains all of the table fields AND a sum field, instead of simply the table itself.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

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