If statments and referring to other tables

[Mike20255]

I am new to access and am having troubles figuring out if statements. I want an if statement in a table that says if column 1 = "whatever" then column 2 = "YAY" else column 2 = "NO".

How would I apply this to a table?

Also I have a table that has one column it is a list of codes. In another table there are 2 columns column 1 has a code i want column 2 to say if column 1 code is found in the list of codes from the other column the put "MATCH FOUND"

Thanks,
Mike

 

[KenHigg]

If the value you want to store in col b is always directly denpendant on the value in col a then I would not even have a col b in the table and simply derive it when ever you need it based on the if() statement you posted.


ken

 

[Mike20255]

Ya that makes sense but I can't figure out how to use an if statement at all....every time I try it just shows no results?

 

[KenHigg]

Something like (in a query):

MyNewField: iif(col1="B", "aaa", "bbb")

 

[Mike20255]

Here is my forumla: IIf([Combined Grantable & Taxable]![Class]="CT","WORK","BLAH")

I put this in the Creteria section of a query under a field that is blank in the table still it shows nothing?

Thanks.

 

[KenHigg]

This:

MyNewFieldName: IIf([Combined Grantable & Taxable]![Class]="CT","WORK","BLAH")

Needs to go on the top row of the query builder grid (not on the criteria row) instead of a regular column name.

 

[Mike20255]

Okay that works thanks alot. I am so confused with this program....I have used Excel quite a bit but don't know where to start in Access

So is this how all functions need to be dealt with in queries? and is there anyway to do if statements in tables?

Thanks again.

 

[KenHigg]

Hum, I'd say most functions. I'd hate to stick my neck out and say all

As for using if, you just used whats call the immediate if - iif()
fyi: If you need more than two options with the logic of the iif(), you can embed iif()s

iif(condition to test, true part1, iif(condition to test, true part2, false part))




ken

 

[The_Doc_Man]

First problem: EXCEL. You do not put IIF statements in a table. They don't belong there. Tables are for storing data.

QUERIES can do what you wanted and, to most other elements of Access, look enough like tables to be usable as such. In other words, you can build a query that has your IIF in a query field, but then refer to table fields when required in that same query. Then open the query as though it were a table. Most of the time, that is viable. Though when you have formulas in a query you might have trouble updating them through the query's datasheet view.

 

[Mike20255]

So if I had a table that had 1 column (Table 1 column "Codes") with a list of 2 string text values that are going to be updated occasionally. How would I have a column in the query called "Description" compare the Codes from Table 1 to another set of codes from another table. Hard to explain:

Table 1
Codes
a
b
c
d
e
f

query using table 1 and table 2
Table 2 Reference #:    Table 2 Code:   FORUMULA COLUMN
1                                b                "MATCH"  'as in it is in table 1
2                                c                 "MATCH"
3                                g                 "NO MATCH"
4                                i                  "NO MATCH"
5                                c                 "MATCH"

Hopefully that makes sense.
Thanks,
Mike

 

[KenHigg]

In a new query, I would bring in table 2. The bring in table1. Now do a relationship from the code column in table 2 to the code column in table1. And have the relationship so that you will get all values from table 2 and only the values in table1 where there is a matching record.

Now in the query builder bring down the ref # column from table 2 and in the next column in the query do something like:

MyMatch: iif(isnull(tbl1.code),"No Match", "Match")

Hope this makes sense...


ken

 

[Mike20255]

Okay that works thanks! Here is another issue I am dealing with that maybe you could help me with

I was trying to come up with a solution for my problem by creating a series of complicated procedures maybe there is an easier way to do this

I have two tables (Table 1 and Table 2) each have similiar columns but store different information. One column that is similiar is called the roll number it is just a unique identifier, and an amount column. In lots of cases there are identical roll numbers in each table.

I want to have a form that the user enters the roll number then clicks a button and the information is exctracted from the two different tables and placed on the form. So if there was the same roll number both fields woudl show information on teh form but if only one roll number was on table 1 then it would show the number and table 2 would show nothing. Here is the temp design:

How would I do this because a form is limited to using only 1 table or 1 query?

 

[cat001969]

You could create a query with an outer join between the 1st and second tables then base the form on that.