Return value on a table based on NumberID

[mari_hitz]

Hi guys,

Hope you are good. I would like to obtain your help on something I thought it was going to be easy but it turned out that it was not. First of all please note that I have searched throughout the forum and on internet and I could not find an answer to my question: is it possible to make a something similar to a Vlookup in Excel on Access?
I have a table with fields
NumberID, Name, Address,Amount and then another table that has NumberID, Name,Telephone,Job Title. I would like to obtain the following:
1) To check which cases are included in the second table. I had created a query, however, if a person appears more than one time, it returns me as many lines as the person appears. In addition, I want to return another field in which I would like to write and insert comments and the query does not let me.
2) Return the telephone number based on number ID. If the person does not appear on the second table, meaning that it does not have one, return the line in Blank. When you do this on Excel you obtain with the vlookup an "N/A".

Can I do all of the above on Access? Thanks in advance!

Regards!

 

[Minty]

In your query design double click on the join between the two tables and experiment with the join properties. A carefully designed left or right join will allow you to do what you want.

You may want to run a first query that selects only the latest phone number, then link to that.

 

[sneuberg]

DlLookUp https://support.office.com/en-us/article/DLookup-Function-8896cb03-e31f-45d1-86db-bed10dca5937 might be part of a solution. You could use the query builder to help you get the criteria part of it.

 

[The_Doc_Man]

In addition, I want to return another field in which I would like to write and insert comments and the query does not let me.

A query becomes unable to update based on an ambiguity. I.e. for some reason, your query that joins something to something else is improperly specified so Access does not know to where it should write any updates. Any SQL aggregate function would also prevent updates since aggregates imply multiple source records feeding a single record in the aggregate-based recordset.