Search from 3 tables and display result from 1 table

[softcrew2014]

Good day.

I just need a little help in creating a query design or code. I have 3 tables (tranhead, trandetail, accountmaster) with the following fields below. I have a requirement where the user will enter a search and the query should look on 3 tables below and compare if the search criteria matches on below fields with red color. If there is a match the query should return whatever rows that matches in trandetail table. I have attached the database for reference. Thank you for helping.

tranhead fields - accountno
- accntname

trandetail fields - tranno
- accountno
- debit
- credit
- remarks
- slno

accountmaster fields - tranno
- trandate


- docno
- ....
- ....
- remarks

[andy49]

Look at the like function with a query where all three tables are joined with criteria in the or rows of the design.

Will the search word ever be a number? Date?

[softcrew2014]

thanks andy.

the search word from the user will be a text or a string. I am still new to msaccess. please find attached db above. thanks.

[softcrew2014]

I have made a query to the database to come up the desired result. I dont know if this is correct. Please check my query in db attached. Thanks

[andy49]

Something like this perhaps

[softcrew2014]

Thanks andy.

Its very close to the desired result. But instead of returning only 1 row at a time, can we make it to return all rows with the same tranno in trandetail table?
Example if the user will search "data", it should return all rows with the same tranno just like image below rounded in read. Thanks.