Dlookup Syntax Q from NewBie

[moradwan]

Hello,
I am an excel user and new to access. I would like to use the dlookup function in an SQL query.

I have created a simple database made up of two tables, lookup_tbl and Data_tbl, to write a practice SQL code using DLookup.

My lookup_tbl is a two column table shown below:

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Lookup_tbl</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">lookup_value</th> <th bordercolor="#000000" bgcolor="#c0c0c0">return_value</th> </tr> </thead> <tbody> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_2</td> <td bordercolor="#c0c0c0">return_2</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> <td bordercolor="#c0c0c0">return_3</td> </tr> </tbody> <tfoot></tfoot> </table>

My Data_tbl is shown below:
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Data_tbl</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">col_A</th> </tr> </thead> <tbody> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_2</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> </tr> </tbody> <tfoot></tfoot> </table>
I would like to write a query in SQL that creates a new table which matches the values of Data_tbl using lookup_tbl and returning the corresponding values in lookup_tbl.

In other words I would like to end up with this:
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Data_tbl</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">col_A</th> <th bordercolor="#000000" bgcolor="#c0c0c0">col_B</th> </tr> </thead> <tbody> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_2</td> <td bordercolor="#c0c0c0">return_2</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> <td bordercolor="#c0c0c0">return_3</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> <td bordercolor="#c0c0c0">return_3</td> </tr> </tbody> <tfoot></tfoot> </table>
So I created a query, lookup_test, and placed the following code in it.

SELECT Data_tbl.col_A, DLookUp("[return_value]","Lookup_tbl") AS col_B
FROM Data_tbl;

However, when I run this query I end up with this:

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>lookup_test</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">col_A</th> <th bordercolor="#000000" bgcolor="#c0c0c0">col_B</th> </tr> </thead> <tbody> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_2</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_1</td> <td bordercolor="#c0c0c0">return_1</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">lookup_3</td> <td bordercolor="#c0c0c0">return_1</td> </tr> </tbody> <tfoot></tfoot> </table>

I know that this is a rookie level question but I can not figure out the syntax for Dlookup. As you can see above it just looked up the first value and returned it for all of the values. Does anyone know the correct syntax?

Thanks in advance
Moradwan

 

[Norie]

Why do you want to use DLookUp?

I justed create a simple query and got these results.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>col_A</TH><TH bgColor=#c0c0c0 borderColor=#000000>return_value</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>lookup_1</TD><TD borderColor=#c0c0c0>return_1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>lookup_2</TD><TD borderColor=#c0c0c0>return_2</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>lookup_1</TD><TD borderColor=#c0c0c0>return_1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>lookup_3</TD><TD borderColor=#c0c0c0>return_3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>lookup_1</TD><TD borderColor=#c0c0c0>return_1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>lookup_1</TD><TD borderColor=#c0c0c0>return_1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>lookup_3</TD><TD borderColor=#c0c0c0>return_3</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Here's the SQL.

SELECT data_tbl.col_A, lookup_tbl.return_value
FROM data_tbl LEFT JOIN lookup_tbl ON data_tbl.col_A = lookup_tbl.lookup_value;

 

[revans]

Your DLookup syntax is off. You want to tell the function what you want: "[return_value]". You want to tell it where you want to get it from: "Lookup_tbl". You had those parts right. You also want to tell it how to choose what to get. So: lookup_value= something.

Dlookup("[return_value]","Lookup_tbl","[lookup_value]='" & Data_tbl.col_A & "'")  as col_B

Notice that I concatenated in the value rather than enclose the entire thing in quotes. Enclosing it in quotes will cause the function to look for a value in the field equal to "Data_tbl.col_A" whereas what I did forced it to evaluate what is in the field and dynamically dump it into the function for each field.

hth,

Rich

 

[moradwan]

allright great.
Thanks for the reply/explanation