nested dlookup 2 tables with true false (1 Viewer)

megatronixs · Jan 7, 2020

Hi all,

I'm trying to get a create a nested dlookup as I think this is what I need.
not sure how it works and all examples I can find are not relevant to what I need.
I need to compare 1 field from 1 table to another field from a second table, I the record is also on the second table, I should get a true, false if not.

I'm using right now 2 test tables for this purpose:
table_1 and table_2. the field names are not equal on both. First table has "walk_1" and second table has "second walk"

Any idea how this can be done? from my understanding "DLookUp" works only on 1 table, and a nested can be used for 2 tables.

thank you all in advance.

vba_php · Jan 7, 2020

megatronixs said:
from my understanding "DLookUp" works only on 1 table, and a nested can be used for 2 tables.

using 1 instance of dlookup() to return values from 2 tables is impossible because the function is only designed to return one value. and if you talk to the others here, they will say that using DL in queries at all is a terrible idea because it's very slow. furthermore you can see in this thread, people like MajP have other solutions that are much better than using a DL function. Take a look at that thread and see if it would be applicable to your situation here.

megatronixs · Jan 7, 2020

hi, not returning from 2 table, just to see if one exist in the other table and then set it as true. unless I understand you correct.
I'm really locked on this and no clue how to build this.

megatronixs · Jan 7, 2020

I found this, but I have no clue how to adjust this:
http://www.utteraccess.com/forum/dlookup-tables-t1935774.html

Gasman · Jan 7, 2020

megatronixs said:
I found this, but I have no clue how to adjust this:
http://www.utteraccess.com/forum/dlookup-tables-t1935774.html

Well it is a nested Dlookup that you were asking for?

Change the names to the names of your fields and tables. ?

I do not expect it matters which you specify first.?

plog · Jan 7, 2020

DLookups have no business in queries. My guess is you are trying to get us to help you implement one step in a sub-optimal solution (and unfortunately this site has become amenable to that).

My advice is post what the ultimate aim of all of this is so that we can help you come up with the best solution. So, throw out the technical jargon (Dlookup, query, etc.) and tell us what the big picture goal is of all this.

Also, a concrete example would help. Tell us the table/field names involved and walk us through an example.

The_Doc_Man · Jan 7, 2020

There is a thing usable through the query wizard called a Find Unmatched query. It helps you find records in one table that don't match records in another table.

Build one of those for your case. Then examine it to see if you can figure out how to REVERSE the logic and show you what WAS matched.

OR look at the logic as giving you the "FALSE" side of the equation. So mark everything as TRUE then modify the Find Unmatched query to set those cases FALSE.

jdraw · Jan 7, 2020

megatronix,

I'm trying to get a create a nested dlookup as I think this is what I need.
not sure how it works and all examples I can find are not relevant to what I need.

I think you should provide more info - in simple terms - about WHAT you are trying to accomplish. You're guessing a solution without defining (clearly) the issue to be resolved.
Good luck.

megatronixs · Jan 7, 2020

Hi Plog,

I need to check data from one table with another table to see if this nr is there too.
It should work like in excel with a vlookup.
example would be like this:
table 1, field name: IDnr:
1234
2345

table 2, field name OtherID:
4567
1234

so, if in table 1 and table 2 the number is there like 1234, I should get True. 2345 is not in table 2, so I should get a False.

all above as an example.

plog · Jan 7, 2020

You didn't provide a big picture context of what this is for, but what you want is a simple Unmatched query as The_Doc_Man described.

The SQL to give you what you want is this:

Code:

SELECT IDnr, IIf(IsNull(OtherID), False, True) AS InTable2
FROM Table1
LEFT JOIN Table2 ON OtherID = IDnr

megatronixs · Jan 7, 2020

Hi Plog,
I get many duplications. In the real table I try to compare, there are like 7449 records, with your query I get 412606 records, in the second table there only 27521 records. All the duplicated 412606 records show as "True"
the excel formula looks like this:

Code:

=NOT(ISERROR(VLOOKUP(T4,sheet2!B:B,1,0)))

.
Maybe that helps a little to understand.

plog · Jan 7, 2020

That means either the values in OtherID or IDnr are not unique. GROUP them and you will have unique values:

Code:

SELECT IDnr, IIf(IsNull(OtherID), False, True) AS InTable2
FROM Table1
LEFT JOIN Table2 ON OtherID = IDnr
GROUP BY IDnr, IIf(IsNull(OtherID), False, True)

IF the records in Table1.IDnr are not unique you will have less than 7119 records. Again, big picture idea of what you are doing would help.

megatronixs · Jan 7, 2020

Hi Plog,
Now I get only 41 records, I need to have all the 7449 with the false or true.
I had to take an excel workbook some one create and transform this into an access tool. there are like 30 formulas that compares data in another sheet. well, there are various sheets with data that are used as dictionaries to check if data in the main table is also available there, if so, the it gets a flag "true" if not, then "false". The main table has over 60 columns, it is an export from another bigger database. this is imported to the tool and then we run the queries to get the flags. So, I have compare one column in the main table to see if it also occurs in the column from the other table.
there are duplicates in the other table column, and in the main table.
This is the trouble I have. in the excel workbook this formula

Code:

=NOT(ISERROR(VLOOKUP(T4,sheet2!B:B,1,0)))

is pretty simple, but I can't get it work in access.

jdraw · Jan 7, 2020

You can try this also, but it would help to know WHAT you are dealing with.

Code:

SELECT Tbl10.idnr, Tbl20.otherid, 'True' AS Expr1
FROM Tbl10 INNER JOIN Tbl20 ON Tbl10.idnr = Tbl20.otherid
union 
SELECT Tbl10.idnr, Tbl20.otherid, 'False' AS Expr1
FROM Tbl10 left join Tbl20 ON Tbl10.idnr = Tbl20.otherid
where  Tbl20.otherid is null;

Sample output:

Code:

idnr	otherid	Expr1
1234   1234	True
2345		False
3456		False
8765		False

megatronixs · Jan 7, 2020

hi Jdraw,
this is very close, I just have to show only one column with true or false. I need to add this to the query I already have.
the numbers I have to compare are actually starting with a "0" as example "0432567" or "0100671" in the database they appear as text. if they are added as number, the 0 disappears. I did a check how many columns in the excel file are, and there are 110 columns. this flag appears in column 102. Sorry if I can't explain it in a different way.
I came across another excel formula that will even complicate more:

Code:

=IF(DD4,VLOOKUP(BT4,Dict_Exceptions!V:AB,3,0),VLOOKUP(CK4,other_Mapping!AE:AL,4,0))

not sure if there is a solution for this one

Greetings.

jdraw · Jan 7, 2020

I am not an Excel person. Perhaps someone else understands your requirement, and excel, and can adjust the example to meet your need.

megatronixs · Jan 8, 2020

is there a way to modify that I will not get unique results?
if I can get the whole 7449 rows with it, it would help a lot.

June7 · Jan 8, 2020

Provide a representative data sample. If you want to provide db for analysis, follow instructions at bottom of my post.

Why do you think you need a nested DLookup?

Domain aggregate may be slow in query for large dataset but might work well enough in this case.

SELECT *, IIf(DLookup("[second walk]", "table_2", "[second walk]='" & [walk_1] & "'") Is Null, False, True) AS HasDup FROM table_1;

or

SELECT *, IIf(DCount("*", "table_2", "[second walk]='" & [walk_1] & "'") = 0, False, True) AS HasDup FROM table_1;

Or build query of table_2 using DISTINCT keyword then join to table_1 in Find Unmatched query.

megatronixs · Jan 8, 2020

hi June7,
almost there. I get all the results from the 7449 rows. but it gives me on all of them '-1'
Looks like he does not compare the second table correct to the first one.

June7 · Jan 8, 2020

It says every value in table_1 has a match in table_2.

Can't help any more unless you want to provide data.

nested dlookup 2 tables with true false (1 Viewer)

Registered User.

Forum Troll

Registered User.

Registered User.

Enthusiastic Amateur

Banishment Pending

Immoderate Moderator

Super Moderator

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Super Moderator

Attachments

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread