Results 1 to 14 of 14
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: How to run this dlookup in a query?

    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
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    132
    thank you,But only simple examples are described in that link. in fact, i don’t know how can i define in the criteria of Dlookup : table1.fieldA=table2.filed1 and table1.fieldB=table2.field2
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well read the reference and make an effort....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    132
    When I set the criteria of Dloockup to a specific number, it works. But I cannot make connection among table records. For example setting the criteria of Dloockup as table2.field1= 10 is fine, but table2.field1= table1.fieldA does not work. Could you provide more help plz?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    could you provide your SQL please
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    it sounds like you should be using both tables in your query and linking them there

  8. #8
    Join Date
    May 2012
    Posts
    132
    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
    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 asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see the SQL then?
    open the query designer, switch to the SQL view
    then copy and paste the SQL from there to here
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2012
    Posts
    132
    Well, I came to this site to ask for the correct query. So I don’t have the right query in hand to write the sql here.
    I made an attempt, however. When I write the following in the sql :
    Expr1: DLookUp("[credit] "," table2 ","jobcat = pharmacy AND product = cosmetics") it 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 would like to know how to correlate between the fields of table 1 and table2 in the criteria part of the Dlookup
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  11. #11
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try creating a query with both tables. In the query design, link the jobcats and product/productsold fields. Drag the needed fields from the appropriate table into the query grid. This should give you the results you're looking for.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    rogue is right, you don't need a DLookup function here. Create a query that links both tables with an INNER JOIN operation:
    Code:
    SELECT Table2.credit
    FROM Table1 INNER JOIN 
         Table2 ON (Table1.productsold = Table2.product) AND (Table1.jobcats = Table2.jobcat);
    Generally speaking, it's better not to use a domain function (DLookup, DCount, etc.) in a query. You can always replace it with a JOIN operation, a WHERE clause or a subquery:
    Code:
    SELECT Table2.credit
      FROM Table1, Table2
    WHERE (Table1.productsold = Table2.product) AND (Table1.jobcats = Table2.jobcat);
    Have a nice day!

  13. #13
    Join Date
    May 2012
    Posts
    132
    thanks a lot
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •