display in a column format

[hexleena]

I have 2 tables - Master plan table and detail table. The plan number is unique in Master plan table and repeats in detail table. There is another column in detail table called planrefnumber that is text, contains values such as 'myp-01-pcs', 'myp-02-pcs', 'myp-03-pcs' and so on.

Based on the value of the column 'planrefnumber' , I want to display a new column that has a 'yes' if planrefnumber = 'myp-01-pcs'. If planrefnumber = 'myp-02-pcs', it should display 'yes' and so on.

Also, is this possible to display all of this in a columnar format, i.e.

plan number planrefnumber disp1 disp2 disp3
H1234 myp-01-pcs yes no no
H1245 myp-03-pcs no no yes

Help will be appreciated!

Thanks.

[janders468]

What you could use is an immediate if (iif). I don't know how you plan to implement this but I would not alter the structure of your table to add these columns. Instead I would create a query that has your planrefnumber column and have three columns in the query that utilize the immediate if.
i.e.
Expr1: iif([planrefnumber]= 'myp-01-pcs', "Yes", "No"

Add a column like that for each of the values you would like to test (You can call it anything I used Expr1 as that is what access defaults to). The iif works like this: the first argument is an expression that evaluates to true or false. In our case that expression is [planrefnumber] = 'myp-01-pcs' the next argument is what it should do if this expression evaluates to true. In our case we want it to display "yes". The next argument is what to display if the expression evaluates to false. In our case this is "No". If you really want to add these columns to your table (which I can't see any good reason for doing) then use the same logic but instead of a select query use an update query and update the columns you have added to your table with the iif expression.

Hope that helps