Kind of duplicate query

[aranj]

I have two tables:

Table a containing personal details (1000 records)

Table b containing some of these people who have done xyz this week (50 records)

I would like to run a query to show those in table a whose surname matches table b.

So if table b contains:

Mr Colin Smith
Mr Alan Jones

I'd like the query to show (from table a):

Mr Colin Smith
Mr David Smith
Mr Douglas Smith
Mr Alan Jones
Mr John Jones

I have messed around with lots of queries, find duplicate wizards etc but still can't crack it.

Thanks in advance for a quick pointer.

 

[barbarossaii]

Hi aranj,

you can do this in the design view of a query.

Just use both table a and b and join the surname fields by dragging one on the other (~> line between both); change the properties of the 'line' ~> should look like an arrow that goes from tableA.surname and points at tableB.surnname.

include all fields from table a and b in the query.

Set the criteria of tableB.surname to "is not null".

This query will return all surnames of table a which also appear in table b.

HTH,
Barbarossa II

 

[Mile-O]

Do your tables contain the compound names or do they have one field for each element of the names? Or si one compound and the other split?

 

[aranj]

Thanks barbarossaii, but it doesn't help much - too many duplicated records.

I have got the joins right by the way ;-)

Also, the specified criteria would get duplicated this way.

So I'd get:

Alan Jones, 55 The Street
Alan Jones, 55 The Street
Bert Jones, 55 The Street

when Bert lives at 22 The Avenue

wouldn't I?

 

[aranj]

Do your tables contain the compound names or do they have one field for each element of the names? Or si one compound and the other split?

Thanks Mile-O-Phile.
There is a field for each element in both tables.
So I really can't see why I'm finding it so hard today!!

 

[EMP]

Try this query:-

SELECT *
FROM [Table a]
WHERE [Surname] in (Select [Surname] from [Table B]);

 

[aranj]

Many, many thanks.