MS Access Query: Using Select Statement within a Switch function or IIF function

Question

I am trying to create a query in which one of two queries is run depending on a specific result. I have Table A and Table B. Table B is sometimes NULL, so I wanted the query to say that if Table B is null, run this query pulling data from Table A, but if Table B is NOT null, then run the query pulling data from Table B.

I tried a switch function as well as an iif, but neither worked. Not sure if that's because I messed up the syntax or if neither of these are valid ways of writing this query?

SELECT 
SWITCH(
DCOUNT("*",Table_B) = 0, 
(SELECT *
INTO Result_Table
FROM Table_A),
DCOUNT(*,Table_B) <> 0,
(SELECT *
INTO Result_Table
FROM Table_B)

SELECT
IIF(DCOUNT(*,Table_B) = 0,
(SELECT *
FROM Table_A),
(SELECT *
FROM A))

Thank you in advance!

Gordon Linoff · Accepted Answer · 2019-11-21 16:45:00Z

1

Assuming they have the same columns, you seem to want union all with not exists:

select b.*
from b
union all
select a.*
from a
where not exists (select 1 from b);

If the columns are not the same, then select the columns that you want -- perhaps using NULL for missing columns from one of the tables.

answered Nov 21, 2019 at 16:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

beatrixb Over a year ago

Thank you! I saw a few people mention unions for similar things, but was thinking of them in the traditional term of just joining two tables together which I didn't want if my Table B wasn't null. Is the where not exists statement here saying if it isn't in Table A, then get it from Table B?

Gordon Linoff Over a year ago

@beatrixb . . . You understand the query correctly.

Collectives™ on Stack Overflow

MS Access Query: Using Select Statement within a Switch function or IIF function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related