query help using a iff statement and relationships

[piersonb]

Here I have three tables.
Control type – has a field control – 1 row
Table1 – has fields: Acct (text), table 1(text), table 3(text) – 8 rows
Table2 – has fields Acct (text), table 2(text) – 4 rows

What I am looking for is to combine Table1 and Table2, replacing table 1 with table 2 if the control type is 4. if its not then it uses table 1.

I was able to do that with the query test except I lose that data in table 3 that doesn’t match table 2.

I would like to have the information kept on Table1.[table 3] and if nothing matches [table 2]

It sounds confusing so I am attaching the database.

The Query test shows it working but losing the extra data from Table1.[table 3]
The Query test2 gives the SQL Err I received when it runs the way I thought It should.

Any help or suggestions would be welcome.

What I am looking for is a way to set a stactic varable that only changes when I set it. and is available for 4 or 5 different queries. run under the same macro.

 

[Jon K]

You can use DLookup() instead of adding the Control Type table in the query.

SELECT Table1.Acct, IIf(DLookup("[Control]","[Control type]")=4, Table2.[table 2], Table1.[table 1]) AS Control, Table1.[table 3] AS Movement
FROM Table1 LEFT JOIN Table2 ON Table1.Acct = Table2.Acct
.

 

[piersonb]

That works perfectly. I converted my very simple examble to my DB and it has eliminated my need to update the query monthly. Thank you.