You could add a new column named Link to table A, of type Lookup into table B, then set a formula on the column Table B.Filter(Name=thisRow.Name), and then from Value 1 and Value 2 do a formula thisRow.Link.Value 1, etc.
However it’s better if you tell us the problem you are trying to solve, since my suspicion is that there are probably better ways to link your data, e.g. by not even having the Name column in Table A, and instead simply do a user-selectable Lookup into table B, or simply do a view of Table B and hide column Value 3.
So, everytime I eat or drink something, I have a button « add drink », « add meal », « add dessert » etc. And those buttons open a form and add a row in a table « Food Recording »
But, I don’t want to fill all the informations like quantities, vitamins, etc.
Then, I have another database with all that informations.
So, when I add a meal for example via the button, via a lookup, it filters the list in “food list” and I choose the exact meal, and it goes to the Name column.
But I want to have the other columns completed too, so that I can have a better view of what I eat (calories etc) day by day.
I hope it helps you Ed to understand what I’m trying to do. (According to me it’s a classic use of databases, isn’t it ?)
You could do this, but it is not going to be simple. You cannot simply record a meal, you would need to break it down into it’s components, and also give an indication of the size of the portions to get to the calories.
Then with the vitamins, etc, you will need not only to the size, but each portion will have different nutrients and micro nutrients.
In fact, I’ll have a table with all the Food and sizes I usually eat.
So that everytime I eat something I just need to create a record in another table, with the date, select from that table what I ate, and add a comment if needed. The rest of the row will be a copy from the table with the list of food.
