struggling with my left join

[ArielZusya]

Alright... so... I have a table (tblNames) and another table (tblQuotes) and
in the tblQuotes table I store quotes associated with a particular name
(stored in tblNames). The tables are as follows:

tblNames.ID_Names (autonumber, key)
tblNames.Name (text, name of the person being quoted)

tblQuotes.ID_Quotes (autonumber, key)
tblQuotes.CaseMatch (number, match to the autonumber in tblNames)
tblQuotes.QuoteMemo (memo, quote of mathed name in tblNames)

The relationship between tblNames.ID_Names and tblQuotes.CaseMatch is one to
many. I use a form with a subform to display the data and manipulate it.

I figured out that I can left join query to show only those records in
tblNames that have records in tblQuotes that are not null. The trouble is if
tblQuote has five quotes in tblQuote.QuoteMemo associated with a particular
record in tblNames the query shows me (as one would expect) the name stored
in tblNames.Name once for each quote associated (so in the above example I'd
get the name five times). Where I'm struggling is, I'd like to use the query
to have the form only show me the records with quotes but not show me the
associated record five times.

In other words if tblNames contains two records:

ID_Names: 1, Name: Joe
ID_Names: 2, Name: Jane

and tblQuotes containes five quotes all associated with ID_Names: 1 (Joe),
the query will return:

Joe, quote1, quote2, quote3, quote4, quote5
Joe, quote1, quote2, quote3, quote4, quote5
Joe, quote1, quote2, quote3, quote4, quote5
Joe, quote1, quote2, quote3, quote4, quote5
Joe, quote1, quote2, quote3, quote4, quote5

Which, of course, means if I use the form and subform I get five identical
records each with five identical quotes. What am I doing wrong? How can I
query for records that have quotes but only have each record from the query
show one record from tblNames? Do I need to do a second left join? This is
making my head hurt. Any help would be greatly appreciated! Thanks!

 

[Ken Snell \(MVP\)]

Try this for your query:

SELECT tblNames.*
FROM tblNames
WHERE tblNames.ID_Names IN
(SELECT tblQuotes.CaseMatch
FROM tblQuotes);

 

[ArielZusya]

Wow... that's awesome! You rock! Thanks for the help!