calculated fields with multiple tables

[databasewolfy]

Hey there. I'm a bit of a newbie to SQL statements, let alone programming.



I have a database with three tables. I want a column to return three values based on three conditions.


Table1
name

Table2
name

Table3
name
foo (calculated field)


Table1.name and Table2.name can never match.
If Table3.name has an entry that matches in Table1.name, then foo = X
If Table3.name has an entry that matches in Table2.name, then foo = Y
If Table3.name does not have an entry that matches in either table, then foo = not found


As far as forms go, can this statement be applied to a control box to just display the appropriate value?

Thanks in advance.

[CJ_London]

in a query left join table 3 to tables 1 and 2 on name

calculated value would be switch(nz(table1.name,"")<>"","X",nz(table2.name," ")<>"","Y",True,"not found")

note that options for calculated values in tables are very limited, certainly what you require cannot be done in a table, only in a query

As far as forms go, can this statement be applied to a control box to just display the appropriate value?

you can apply the above formula to the controlsource of a form or report control or simply include in the form/report recordsource. As a controlsource your form/report recordsource would need to include table1.name and table2.name. As part of the recordsource you would just need to bring through the formula result

[databasewolfy]

in a query left join table 3 to tables 1 and 2 on name

calculated value would be switch(nz(table1.name,"")<>"","X",nz(table2.name," ")<>"","Y",True,"not found")

note that options for calculated values in tables are very limited, certainly what you require cannot be done in a table, only in a query

you can apply the above formula to the controlsource of a form or report control or simply include in the form/report recordsource. As a controlsource your form/report recordsource would need to include table1.name and table2.name. As part of the recordsource you would just need to bring through the formula result

I'm afraid I don't understand what you mean by "left join"

[CJ_London]

when you create a query, you join the different tables together

once you have done this, right click on the join line and select the option 'include all records from table 3 and only those records from table 1 where there is a match'

[isladogs]

This article explains the different types of query join with examples http://www.mendipdatasystems.co.uk/q...pes/4594517491