I have 2 table containing the same type of data (for different year). My question is, how can I make queries based on user selected year on a searchbox provided?
I have 2 table containing the same type of data (for different year). My question is, how can I make queries based on user selected year on a searchbox provided?
Well, the first thing is that is not a real good design. Normally you would store the same data in the same table with some sort of designation like year or month and year etc. to be able to pull it.
That being said, you could create a query which then you can have criteria against.
SELECT T1., 2020 As Year
FROM Table1 As T1
UNION
SELECT T2., 2021 As Year
FROM Table2 As T2
Save that query and then
in another query you can set the criteria
SELECT * FROM qryMyUnionQueryName
WHERE Year = Forms!MyFormName.txtYear
The fields of both tables need to be the same data type for each corresponding field and in the same order to use the asterisk. Otherwise you will need to list each field out separately and still the datatype of each corresponding field will need to be the same.
At first I thought by making a separate table would be easier. I decided to follow your suggestion and make it into 1 table. Thank you for taking time to solve my problem.
4 people are following this question.