question

AhmadAriefMohdNor-2381 avatar image
0 Votes"
AhmadAriefMohdNor-2381 asked AhmadAriefMohdNor-2381 commented

single Queries from multiple table

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?

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 answered AhmadAriefMohdNor-2381 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·