Clever Geek Handbook

Where is the function in virtual column in combined query

I am new to sql and I need a little help. I have a query union

with 3 tables. I have a virtual column so I can figure out which table each row is from. I would like to be able to filter this virtual column to only display rows from a specific table.

I am using Microsoft Access and this is what I have so far:

SELECT Table1 as Table_Name, Table1.1  
FROM Table1  
UNION ALL  
SELECT Table2 as Table_Name, Table2.1  
FROM Table2  
UNION ALL  
SELECT Table3 as Table_Name, Table3.1  
FROM Table3  
UNION ALL  
WHERE Table_Name = Form1.TextBox  
ORDER BY Table1.1;

I am trying to link this to a list box on a form and then add some text boxes to filter the results.

+3


source to share


1 answer


What about:

SELECT * FROM
( SELECT Table1 as Table_Name, Table1.1
FROM Table1
UNION ALL
SELECT Table2 as Table_Name, Table2.1
FROM Table2
UNION ALL
SELECT Table3 as Table_Name, Table3.1
FROM Table3 ) q
WHERE Table_Name = Forms!Form1!TextBox
ORDER BY Table1.1;


See: http://access.mvps.org/access/forms/frm0031.htm

+6


source






More articles:


All Articles