SQL / MS-Access - building query based off user input in another table
I have a user maintained table that contains field names and criteria in it, example below:
(USERTABLE) Name_of_query field_name criteria_input Query1 customer Jim Query2 address null Query3 etc ...
I have another table with values the user wants me to pull specific records from based on the values in the first table
I.e, they only want the first record in the below table when using query1
(CUSTOMERTABLE) Customer phone address status Jim 222-555-6666 (22 front street) active Carl 333-444-7777 (123 back street) inactive
How do I pass the user values into my query? In the above example I'd need to use customer and jim in my query, but I can't think of a way to tie it back to the user table for the fields in the record corresponding to query1.
EDIT: Relevant to add that I'm attempting to use this SQL statement in MS Access. Also, would I be able to use "DLookup"?
SELECT * FROM CUSTOMERTABLE WHERE CUSTOMERTABLE.[Dlookup("field_name","USERTABLE","Name_of_query = 'Query1'")] = Dlookup("Criteria_Input","USERTABLE","Name_of_query = 'Query1'")
This prompts me for a parameter, not sure why.
You should be able to use a Subquery to get your value out of the first table. The inner Select will run first and give you a value you should be able to do something with like stuff in a variable or something
This is kinda ruff but you could do something like
DECLARE @QueryVariable AS NVARCHAR(20) Select Table2.ColumnName1, Table2.ColumName2, (Select @QueryVariable = Table1.ColumnName1 ) FROM Table1 From Table2 WHERE Something = @QueryVariable
Might also want to consider parameterizing the outer statement, I don't know how confident you are in the data coming from the first table. ie Mr. Drop Tables / SQL Injection