Search + Link to Other Table

Background: I have two tables in a Question + Answer database:

  • Questions: Where all the questions are
  • Answers: Where the answers to the questions are

Answers can be used to answer many of the questions - so there is a one to many relationship from Answers to Questions.

The Question: How can I allow a user to search and select an answer for a question - to establish this link?

So that afterwards I will be able to:

  1. See the ANSWERS tab an ‘inline list’ of all Questions the Answer is linked to.
  2. in Questions Tab - display the answer attached to it via a LOOKUP field

I hope this is clear, please let me know if I haven’t clarified or explained anything well.

May I ask how large is the database (how many questions)?

Have you considered combining the data into one table? Something like Question #’s in column A,
Questions in column B, Answers in column C?

Thanks @Eric_Penn.

I did, however that doesn’t work with the 1 to many relationship.

Many questions can have the same answer. So looking at it from an object perspective, they need to be separate tables.

Does that make sense? Am I missing something?

Can you share some screenshots of your data and what you are trying to do we can have a little more context? I guess it’s not quite clear to me what you are looking for. Sounds like you have a Relation from Answers to Questions…but do you also have a Relation from Questions to Answers?