vlookup into table from query (1 Viewer)

araza123

New member
Local time
Today, 05:20
Joined
Apr 6, 2022
Messages
24
Hi guys,

the title kinda describes what is need. Is is possible to do this? I have a query I ran with data. Theres a field called "LTV". I need to replace the data from this field to a table I have created. the table also has a LTV field. I have a common unique identifier to do the lookup on if this was excel but not sure if its possible in access. Thanks for your help
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2013
Messages
16,553
DLookup is the equivalent to Excel's VLookup

Something like this in a query

luLTV:Dlookup("LTV","OtherTable","UniqueID=" & [UniqueID])

replace field and table names as required
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:20
Joined
Sep 21, 2011
Messages
14,050
Join the tables using your unique identifier?
 

plog

Banishment Pending
Local time
Today, 05:20
Joined
May 11, 2011
Messages
11,613
Dlookups have no place in queries.

Instead you use SQL and JOIN any datasources you need. In design view, bring in whatever table has your LTV field, JOIN it to whatever existing datasource it should go to using the field that connects them, then bring down the LTV field into the query below to show it.

Here's a great source for learning about SQL:

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:20
Joined
Feb 19, 2002
Messages
42,981
Make sure to use a LEFT join if the lookup is optional

Select t1.fld1, t1.fld2, t1.fld3, t1.LTV, t2.LTVtext
From t1 Left Join t2 on t1.LTV = t2.LTV;

The reason we don't use dLookup()s in queries or VBA loops is because each one runs a separate query. Therefore, if you have 1000 rows to retrieve, you will need to run 1001 queries. When you are dealing with small amounts of data, it doesn't much matter what you do so domain functions are frequently offered as solutions. But there are far more efficient methods and you might as well learn them from the start. I once had an assignment to help a bunch of analysts who were working with Excel to transition to Access for some of their data manipulation requirements. The team used to come into work in the morning, download yesterday's data from our provider into their spreadsheets and start their VLookup macros. Then they went out for breakfast. Two hours later they would come back to start their day. The files were huge with millions of rows so each row was running a separate query (VLookup() is a query just as DLookup() is) I taught them how to import the data into Access and using a join, have the data ready for use in their spreadsheets in about 3 minutes. Management was delighted but at least the analysists didn't hate me because Management offered to order breakfast in for them every day. Win, win.
 

Users who are viewing this thread

Top Bottom