Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    11

    Unanswered: How to populate one table column based off another [newbie]

    Hello. I'm fairly new to databases, I've done some work on making basic Access databases with multiple tables and using SQL to populate/edit/remove entries. Please forgive me if this question has been up before.

    Now I am tasked with creating an Access database based off an Excel document. In the Excel sheet some columns were automatically populated using standard "=C1+D1" logic. However I have no idea how to make that happen in Access!

    I have attached a file for you to visualize what I mean. I would like for the column "Klar" to be populated by the values from "Datum" and adding a week.
    In SQL that would be something like
    UPDATE [Order] SET [Order].Klar = Datum+7;
    Can this be done automatically?
    Attached Thumbnails Attached Thumbnails untitled.GIF  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Welcome to the forum

    First thing - what is good in a spreadsheet often <> what is good in a relational database.

    The simple answer is you would not - you would simply calculate this as and when you require this information.

    The long answer is get to grips with relational database design and normalisation (especially third normal form and transitive dependencies).
    http://www.tonymarston.net/php-mysql...se-design.html

    HTH

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also no - in JET (the underlying database engine for Access) it cannot be done automatically. Some RDBMS products have what are called calculated columns. JET does not.

  4. #4
    Join Date
    Jan 2008
    Posts
    11
    Thanks for the reply. Is there any way to make it happen as a Form data sheet? Like some kind of macro when you enter that column. Thanks for your patience

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ya can. Easiest way (as I recall) is to use VBA to set the value of the text box:
    Code:
    Me!KlarTextboxName.Value = Me!DatumTextboxName.Value + 7
    and have this fire on the afterupdate event of your datum control source.

    My advice is still that this is the wrong thing to do (assuming of course that klar should always & at all times be 7 days hence from datum).

  6. #6
    Join Date
    Jan 2008
    Posts
    11
    That worked perfectly, thanks a bunch. I know it's probably taking the easy way out for now, but that link you gave me seemed to be quite a hefty read
    I'd like to know why you would consider this method to be "the wrong thing to do", if you don't mind.

    This seems like a great place, I'll stick around for a while as I'm sure I'll have other questions in the near future.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Because it seems easy in the first instance but turns out to be much harder in the long run. Have a read of the document. It is a hefty read - that is why it is worth reading Some people (including some posters on here) reject quite a few of the tenets of normalisation. That's fine - but you should understand first of all what you are rejecting.

    The big problem is what happens if Datum changes. Should Klar change to reflect it (in which case storing a value for Klar is wrong) or should Klar remain as it is (in which case storing the value is quite correct).

    Remember that I am not simply saying "don't do that" and leaving you out on a limb. I am saying "Do something else instead".

  8. #8
    Join Date
    Jan 2008
    Posts
    11
    I have a related question but I'm having trouble phrasing it. My Access is in Swedish and I'm not familiar with all the terms in English.

    Right now my main table consists of normal input fields, a primary key number and fields connected by number to other tables, using the wizard thingy where instead of selecting "number" or "text" you select another table to associate with (see picture in original post for relationships).

    Now, by using the code pootle flump so kindly gave me I can change the numerical value of a column:
    Code:
    Me!KlarTextboxName.Value = Me!DatumTextboxName.Value + 7
    This works fine for changing normal input fields on the main table. But when changing the reference to other tables the code gets "ugly". Let's say I have a sub table with the following information: keyID=1 text="firstRow", keyID=2 text="secondRow". Now by using the main table in the same manner as before I can change what is being presented by changing the reference, for example from "firstRow" to "secondRow":
    Code:
    Me!Kund.Value = 2
    But that isn't good coding. If for some reason the keyID is changed in the sub table then one would have to re-do all the code with new numbers.
    Is it possible to do it in a smoother way? Or am I just barking up the wrong tree?

  9. #9
    Join Date
    Jan 2008
    Posts
    11
    Am I making any sense here?

Posting Permissions

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