Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2017
    Posts
    3

    Unanswered: Automatic value in one field depending on values in an another table

    Hey
    First post here. Just startet using Access together with excel an the first real problem has occurred

    I have a table1 with field1, field2 field3 and field4,
    another table2 with fieldA,
    yet another table3 with fieldAA.

    In table1.field4 I want the value to be 1 if the value in table1.field1 = table2.fieldA and table1.field2 = any of the values in table3.fieldAA
    Else the value will be 2.

    When the values in table2 and table3 change I want te values in table1.field4 to change automatically.

    Anyone have a solution to this??

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 34
    You could make an Update query.
    Put in your conditions, and set the field to update if they are correct.

  3. #3
    Join Date
    Feb 2017
    Posts
    3
    I tried but I'm not sure how to compare the value in table1.field4 to all the values in table3.fieldAA.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Quote Originally Posted by Rubicon80 View Post
    I tried but I'm not sure how to compare the value in table1.field4 to all the values in table3.fieldAA.
    Have you Used the Dcount("*",[tablename],"[feildname]=" & thisvalue)

    note if you checking strings use the " or the ' at each end
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  5. #5
    Join Date
    Feb 2017
    Posts
    3
    Quote Originally Posted by Rubicon80 View Post
    I tried but I'm not sure how to compare the value in table1.field4 to all the values in table3.fieldAA.
    I'm a bit noob at this but I can't see how those conditions is gonna solve my problem.

  6. #6
    Join Date
    Dec 2016
    Posts
    22
    I have a form that has a search field call this query

    Code:
    SELECT *
    FROM InventoryMasterT
    WHERE (((InventoryMasterT.[ItemDescription]) Is Null));
    The * finds all items in the table InventoryMasterT in the column ItemDescription and then shows those items. I use a split form, so I have a form on top, and table on bottom.

    It is very effective. Notice this is not a query built in the query builder, but a query called by clicking the 3 dots next to the field "Record Source" under Data properties.

    Hope this helps.

Tags for this Thread

Posting Permissions

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