How to run this dlookup in a query?

[ariansman]

There are two tables: Table1 and Table2. A query is built on Table1. In an Dlookup expression We want the query show us the record of filed3 from table2, if filedA from tabl1 equals Field1 from table2 and fieldB from table1 equals field2 from table2.
Thank you

 

[pr2-eugin]

Hello, Why do you not perform a simple Join? That way it will pick up the Value of the Field field of the table2 table for the corresponding field in table1.. The query will be something like..

     SELECT Table1.*, Table2.field  FROM Table1 INNER JOIN Table2 ON Table1.filed1 = Table2.field1;

This link will give some idea on INNER Join..

 

[ariansman]

thank you, but i am so confused in joining queries. it is too complicated for my level. so isnt that possible by Dlookup?

 

[rodmc]

thank you, but i am so confused in joining queries. it is too complicated for my level. so isnt that possible by Dlookup?

If your table relations are correct, it should be no problem. When you add related tables to the query (if you are in design view) you will see the relations between them, then when you drag the desired fields to the query grid the joins will be created for you. (it will create an inner join by default)

 

[ariansman]

could you please confirm that Dlookup will not help in such case and i should only try to fix it through inner join?

 

[rodmc]

could you please confirm that Dlookup will not help in such case and i should only try to fix it through inner join?

I would say yes, DLookup is not required, its just a case of making sure youre table relations are correct, a simple SELECT query would suffice for what you want to do

Can you post you DB?

 

[ariansman]

this is the DB:
Table1: fields: ID, jobname, jobcats , managergender, rural, productsold
Records:
1,jacksons, pharmacy, male, yes, cosmetics
2,stevens,pharmacy,male,no,cosmetics
3,robert,doctor,female, yes, therapeutic,
4, George, pharmacy, female, yes, complement
5, cyrus, pharmacy, male, no, cosmetic
.
.
.
.
6000 – darius, doctor, male, no, therapeutic,

Table2: fields: ID , jobcat, product, credit
Records:
1,pharmacy, cosmetics,20
2,pharmacy, therapeutic, 30
3,pharmacy, complement,40
4,doctor, cosmetic,25
5,doctor, therapeutic, 30
6,doctor, complement,15
End of records
The query is built upon table1 and includes Expr1: Dlookup( “[credit]“, table2”, “ table1.jobcats=table2.jobcat and table2.product=table1.Productsold”)
If this Dlookup works well, it will show the following:
1- 20
2- 20
3- 30
4- 40
5- 20
.
.
.
.
600- 20
i also made an attempt:
I made an attempt, however: the following expression
Expr1: DLookUp("[credit] "," table2 ","jobcat = pharmacy AND product = cosmetics") works only for the compatible records, but for others, for example when the job is a “doctor”, it shows blank. It seems Dlookup only shows data from table2 and i am hopeful to know how to correlate between the fields of table 1 and table2 in the criteria part of the Dlookup
thank you

 

[pr2-eugin]

DLookUp will not work as you expect it to be.. Only a JOIN will be able to extract what you actually desire.. Try this Query..

SELECT Table1.ID, Table1.jobName, Table1.jobcats, Table1.managergender, Table1.rural, Table1.productsold, Table2.credit
FROM Table2 INNER JOIN Table1 ON (Table2.product = Table1.productsold) AND (Table2.jobcat = Table1.jobcats);

You know what will be easier.. if there is another field on the table1 that will be the FK of the ID field of table2.. So you can do it much simpler..