Variable select statement in access 2016

Question

I'm trying to figure out how to do a select statement that changes what it's selecting based on criteria and that scales on it's own (more or less). In my reference table I have the criteria as well as the fields that I want to return, but no other fields. Below is a simplified table.

Reference Table

Criteria	Field1	Field 2	Field3
A	Yes	No	No
B	No	Yes	Yes
C	Yes	Yes	Yes

Data Table

Other stuff	Criteria	Field1	Field 2	Field3
yale	A	1	1	1
same	B		1	1
atlantic	B	1	1	1
english	D	1	1

Expected Return

Other stuff	Criteria	Field1	Field 2	Field3
yale	A	1	NULL	NULL
same	B	NULL	1	1
atlantic	B	NULL	1	1

What I want is only the fields that have a yes in the reference table where the Criteria field in both tables match (if criteria is 'A' only return Field1, etc) per record. I have some additional math functions to run on these results, so i need the values returned. Any help the community can provide will be greatly appreciated. Thank you. (P.S. I Have MS Access 2016)

June7 June7 · Accepted Answer · 2021-01-20T23:02:17

Join the tables and use IIf() expressions. Assuming Field1, Field2, Field3 in Reference table are yes/no type, consider:

SELECT Data.*, 
IIf([Reference].[Field1],[Data].[Field1],Null) AS F1, 
IIf([Reference].[Field2],[Data].[Field2],Null) AS F2, 
IIf([Reference].[Field3],[Data].[Field3],Null) AS F3
FROM Data INNER JOIN Reference ON Data.Criteria = Reference.Criteria;

Variable select statement in access 2016

1 Answers