nested dlookup 2 tables with true false (1 Viewer)

megatronixs

Registered User
Joined
Aug 17, 2012
Messages
719
Hi June7,
not all in table 1 have a match in table 2, and in table 1 could have various entries with same value. for example 20 rows in table 1 from the 7449 rows could have "01763789"
Table 2 is a collection records that they keep adding that comes from another table. then table 1 is checking if this nr was also in table 2, if match, a true, if no match a false. I can't upload no data to the forum as we have all locked on our pc's. This makes it harder to explain with no data visible. There will be no unique values in the rows in either one.
example how it looks like:

table 1 table 2
1234 1234
2345 2345
1234 9512
1234 9654
2345 7541
7654 6235
7654 8542
7654 9654
5235
4589
2587
4521

(the last 4 numbers on the left should be on the right, formatting is keeping them to the left)

outcome
1234 TRUE
2345 TRUE
1234 TRUE
1234 TRUE
2345 TRUE
7654 FALSE
7654 FALSE
7654 FALSE
7456 FALSE
6431 FALSE
1234 TRUE

in excel this formula:
Code:
=NOT(ISERROR(VLOOKUP(T4,Sheet2!B:B,1,0)))
is looking for a match from column T4 (row 4)in sheet 1, in sheet 2 whole column B

I'm pretty sure that the simple formula in excel should be also something pretty simple in access, but I have no clue as I never had to do something like this.
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,691
Shouldn't have shown tables side by side.

Just to confirm, I built tables with this data. Query outcome is as shown in your example, except for 7456 and 6431 which are not shown in Table_1 sample data. True/False shows correctly for other values. Same with this version:

SELECT Table_1.walk_1, IIf([walk_1]=[second walk],True,False) AS HasDup
FROM Table_1 LEFT JOIN (SELECT DISTINCT [second walk] FROM Table_2) AS T2 ON Table_1.walk_1 = T2.[second walk];

And another:

SELECT walk_1, IIf([walk_1] IN (SELECT [second walk] FROM Table_2), True, False) AS HasDup FROM Table_1;
 
Last edited:

megatronixs

Registered User
Joined
Aug 17, 2012
Messages
719
hi June7,

the above did the trick :) it is working great now.
Thanks a lot.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom