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:
SELECT Table1.FieldA, Table1.FieldB, Switch([FieldA] & [FieldB]='13','US',[FieldA] & [FieldB]='21','CA',[FieldA] & [FieldB]='38','EN') AS FieldC
You can also build a "pure" SQL solution by using a lookup table:
1. Table Tbl_LookUp with 3 columns and 3 rows:
FieldA FieldB FieldC
1 3 US
2 1 CA
3 8 EN
2. The query:
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.
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?
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 formMe 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.