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.

1 answer

  • answered 2020-10-16 04:37 good light

    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