Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2011
    Posts
    48

    Unanswered: Defining a fields value based on two others

    Hi,

    My first post so please forgive me if the question is very basic.

    I have a table that includes blank fields. I want to be able to do the following. With field c being the blank one, and you enter field a/b

    If field a = 1
    and field b = 3
    field c = US

    If field a = 2
    and field b = 1
    field c = CA

    If field a = 3
    and field b = 8
    field c = EN

    Is this possible without making a query? Can you do it with expression builder? macro builder?

    thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your database is not normalized: You should not store a value that can be computed from other values in the same table.
    See: Database Design :: Normalization Basics - Techniques
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    48
    well I don't really want my database to compute anything, more just I want field c to be a certain value based on what a/b are, but c can only be 3 different fields.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Call it as you please, you still want to store a value that can be derived, inferred, deduced, ... from two other values in the same table.

    There are no triggers or computed columns for tables in Access and the possibilities of defining a default value for a column are very limited, so there is no real solution that would guarantee that the contents of FieldC will always be coherent towards the contents of FieldA and FieldB.

    Moreover you do not specify what happens to fieldC when FieldA and FieldB contain values different from (1, 2, 3) and (3, 1, 8) respectively, so I'll assert FieldC is Null in those cases.

    You can only compute the Value of FieldC in a query, a form, a report or a function, using VBA, SQL or a combination of both.

    There are several solutions to compute the value of FieldC, most of them using If, IIf or Select Case constructions. Here's one other possibility in a single line that can be used in a query:
    Code:
    SELECT Table1.FieldA, Table1.FieldB, Switch([FieldA] & [FieldB]='13','US',[FieldA] & [FieldB]='21','CA',[FieldA] & [FieldB]='38','EN') AS FieldC
    FROM Table1;
    You can also build a "pure" SQL solution by using a lookup table:
    1. Table Tbl_LookUp with 3 columns and 3 rows:
    Code:
    FieldA FieldB FieldC
       1      3     US
       2      1     CA
       3      8     EN
    2. The query:
    Code:
    SELECT Table1.FieldA, Table1.FieldB, Tbl_LookUp.FieldC
    FROM Table1 LEFT JOIN Tbl_LookUp ON 
        (Table1.FieldB = Tbl_LookUp.FieldB) AND 
        (Table1.FieldA = Tbl_LookUp.FieldA);
    Both solutions return Null in FieldC for any other combination of values in FieldA and FieldB.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    48
    thanks so much sinndho!

    That was what I was looking for, and I may not have been explaining it properly. I am doing it in a form sorry about that.

    I will try and do it, and if I have any more questions I will post back.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome! Come back if you need more.
    Have a nice day!

  7. #7
    Join Date
    Sep 2011
    Posts
    48
    I must be doing something wrong, please forgive me I am very new at ms access. When I try doing what you gave me it gives me an error. I tried just using the example I gave, made a table called table1 with fieldsA and fieldsB.

    I tried using a simpler form just for me to get started. In my query design view I have 3 columns used, one for fieldA, FieldB and FieldC. FieldA/FieldB are from the table and in the third column it makes the name Expr1: [fieldsC].

    In the criteria part of Expr1: [fieldsC] I typed in the following:

    (SELECT Table1.FieldA, Switch([FieldA] ='1','US',[FieldA] ='2','CA',[FieldA] ='3','EN') AS FieldC FROM Table1

    I get an error, when I try to run the query, that reads:

    "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

    First am I putting the select statement in the right place? Also, if not can you let me know where?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. create a query, (lets suppose you name it "Qry_FieldABC"), switch in SQL view and paste this:
    Code:
    SELECT Table1.FieldA, Table1.FieldB, Switch([FieldA] & [FieldB]='13','US',[FieldA] & [FieldB]='21','CA',[FieldA] & [FieldB]='38','EN') AS FieldC
    FROM Table1;
    Have a look at the attached picture to see how it looks like in Design view.

    2. Assign "Qry_FieldABC" to the RecordSource property of the form you want to use. If you now open the Field List Window you'll see that 3 fields are available: FieldA, FieldB and FieldC.
    Attached Thumbnails Attached Thumbnails Qry_FieldC_Switch.jpg  
    Have a nice day!

  9. #9
    Join Date
    Sep 2011
    Posts
    48
    Oh thanks so much!!

    Going to continue building my form, if I have any more questions I will be sure to come back

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  11. #11
    Join Date
    Sep 2011
    Posts
    48
    Hopefully you check back to this thread !!

    So thanks to your help my form is working, now I want to add some cosmetic details.

    Is it possible to create a button that would change a field from either having "y" in it or it being blank?

    Basically what happens now is if you enter "y" into a field a whole bunch of other fields are automatically populated, so I was wondering if this is possible?

    thanks!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by grooner10 View Post
    Basically what happens now is if you enter "y" into a field a whole bunch of other fields are automatically populated
    Do you mean that there is an event handler for the AfterUpdate event for the control bound to this fields (let's call them Field_Y (field) and Txt_Field_Y (textbox control)?

    If it's the case, you cannot use:
    Code:
    Me.Txt_Field_Y.Value = "y"
    or more simply:
    Code:
    Txt_Field_Y = "y"
    This is because modifying the Value property of a TextBox does not trigger the AfterUpdate event.

    This should work (let's call the command button Cmd_SetY):
    Code:
    Private Sub Cmd_SetY_Click()
        Me.Txt_Field_Y.SetFocus
        Me.Txt_Field_Y.Text = "y"  
        Me.Cmd_SetY.SetFocus
    End Sub
    If you want to implement a "flip-flop" mechanism (i.e. when the value is "y" it becomes empty and when it's empty it becomes "y"), the code becomes:
    Code:
    Private Sub Cmd_SetY_Click()
        Me.Txt_Field_Y.SetFocus
        IIf(Me.Txt_Field_Y.Value = "", Me.Txt_Field_Y.Text = "y" , Me.Txt_Field_Y.Text = "")
        Me.Cmd_SetY.SetFocus
    End Sub
    If the field to which the TextBox is bound (Field_Y) accepts Null values, you'll perhaps have to replace "" by Null:
    Code:
    IIf(IsNull(Me.Txt_Field_Y.Value), Me.Txt_Field_Y.Text = "y" , Me.Txt_Field_Y.Text = Null)
    Have a nice day!

  13. #13
    Join Date
    Sep 2011
    Posts
    48
    there is no afterupdate in the field. I built the form using the sql you helped me with early so when the box has "y" in it, many fields are updated (through the query).

    This looks good I'll try it out!!

    Thanks!

  14. #14
    Join Date
    Sep 2011
    Posts
    48
    ok I am a little confused (as always)

    If my form is called start, does that replace the me?

    I can't get it to just input "y" when it clicks, it gives me a debug error, this is what it looks like:

    Private Sub cmd_sety_Click()
    start.[Ours? (Yes/No)].Value = "y"
    End Sub

    the field name is "Ours? (Yes/No)"

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. The query I provided yesterday is a SELECT query. It will never operate any modification on the data (you can consider it's a "one-way" query if you want). For doing what you want you need two things:
    - An UPDATE query or some VBA code to perform the modifications on the data into the table and subsequently "refresh" (requery) the data source of the form using the Requery method.
    - A mean to know when the modification was made into the control ("y" changed to "" or "" changed to "y"), and thus when it's time to modify the data using the aforementioned query or VBA code. This in when the AfterUpdate event comes in handy.

    2. Me is a shortcut to the current form: If the form name is MyFirstForm, when inside the class module of this form Me equals Forms!MyFirstForm. You cannot simply use the name of the form alone.

    3. You should avoid using punctuation marks and other non alphanumeric characters in the names of your objects (Fields, Controls, Forms, etc.): those oblige you to enclose the names in square brackets, renders your code more difficult to read and eventually will cause problems and/or errors in the application. Try to stick to A-z, 1-9, and the underscore (_) character.
    Have a nice day!

Posting Permissions

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