lookup all values against all values only gives me one value back?

[efandango]

I want to say...

Lookup any of these values in that table and display any that match records
on this table; but my Lookup only returns the first record match. How can I
get it to return ALL records that match against All records on the form.

This is my Address form SQL: (Which contains all the records that I want to
match, using [StreetNameID] for the matches.

SELECT tbl_Street_Joiner.Address, tbl_Street_Joiner.Joiner_Title_ID,
tbl_Street_Joiner.StreetNameID, tbl_Street_Joiner.OrderSeq,
tbl_Street_Joiner.Street_Name_Joins_ID, tbl_Street_Joiner.Junction
FROM tbl_Street_Joiner
ORDER BY tbl_Street_Joiner.OrderSeq;

This is my duplicate records SQL: (That contains the records that I want to
match against)

SELECT [Qry_Street_Joiner Find duplicates for].StreetNameID,
[Qry_Street_Joiner Find duplicates for].Street_Name_Joins_ID,
[Qry_Street_Joiner Find duplicates for].Joiner_Title_ID, [Qry_Street_Joiner
Find duplicates for].Junction, [Qry_Street_Joiner Find duplicates
for].Address, [Qry_Street_Joiner Find duplicates for].StreetNameID
FROM [Qry_Street_Joiner Find duplicates for]
WHERE ((([Qry_Street_Joiner Find duplicates
for].StreetNameID)=DLookUp("[StreetNameID]","Qry_Street_Joiner Find
duplicates
for","[Forms]![frm_Runs]![frm_Street_Joiner_Main].[Form]![frm_Street_Joiner_Sub].[Form]![StreetNameID]")));


The main address form is: 'frm_Street_Joiner_Main'

The duplicates form is: frm_Street_Joiner_duplicates

 

[Michel Walsh]

Make a join between the 'this table' and the 'lookup table'. In fact, you
just have to bring the looked_up table in the FROM clause, and remove the
DLookup from the WHERE clause, to read:

SELECT whatever
FROM [Qry_Street_Joiner Find duplicates for] AS a, [Qry_Street_Joiner Find
duplicates for] AS b
WHERE a.StreetNameID = b.StreetNameID



I added the alias a and b for your tables (easier to type, nothing more).
Your DLookup third argument seems incomplete (which field should be equal to
FROMS! ... ? ) but you can add a complete criteria, if required, in the
WHERE clause (or in the graphical editor for your query, if you feel more
comfortable using it).



Hoping it may help,
Vanderghast, Access MVP