MS Access - Selecting which table to merge-CodePudding

Is there a way to choose which table do I merge using SQL in MS Access through entering parameter value?

Let's say I have 3 tables: Users, 2021 and 2022.

Any my query is:

SELECT 
    Users.code, 2021.consumption
FROM 
    Users  
INNER JOIN 
    2021 ON Users.code = 2021.code
WHERE 
    (((Users.code) = [Insert the user code]))

I get "Enter Parameter Value" window to filter the code that I want to check, but I would like to get the same to choose other table (2022)

CodePudding user response：

You can't have table names as parameters as far as I know, but you could do something like this:

SELECT 
    U.code, Yr.consumption
FROM 
    Users as U  

    INNER JOIN
       (select * from [2021] Yr where  [Insert Year]=2021
        UNION ALL
        select * from [2022] Yr where  [Insert Year]=2022
       ) as Yr
    ON U.code = Yr.code 

WHERE 
    (U.code = [Insert the user code])

You need to match the query to the existing/new tables; or pre-create them.