Create IN criteria from another table

  • Thread starter Thread starter fambaus
  • Start date Start date
F

fambaus

Hello -
I have a table which I called Reference: Exceptions.
It is composed of two fields:
FieldName
FieldValue
I would like to use this table to use in Query Critieria to create an IN
list or NOT In list based on the values in the table.

For example:
Say my table had
FieldName FieldValue
State MA
State CT
Empl_No X42
Empl_No Y76

I would like to use the say something like
Select Empl_No from Empl_table
where empl_no = Field Value from Reference: Exceptions and FieldName = Empl_no

I could do something similar on another table the has the State field..
I think I'm close just missing on building block..

Thanks
 
I thnk I solved this myself. It kind of came to me as I wrote my question.

What I did was;
1. Create a query of the Reference: Exceptions table for the field I wanted.
(SELECT [Reference: Exceptions].FieldValue
FROM [Reference: Exceptions]
WHERE ((([Reference: Exceptions].FieldName)="Emp_No"))))
2. Create a basic select query on the table in question with just the one
field selected.
3. Switch to SQL view of the Query, add Where Emp_no IN (
4. Cut and paste the SQL from step 1 into the SQL view of the query.
5. Run query and get the expected results.
 
Back
Top