Queries on One to Many Tables (All)

Topic

New Reply

I have two tables, tblOwners and tblHomes, linked in a one-to-many relationship. tblHomes has a field HomeType which is a numeric code for the type of home. Any owner can have none, one or several of any type of home.

I need to get a list of owners who have homes of a particluar type. When I apply the following SQL (shorn of all the superfluous fields) I get lots of duplicated owners, i.e. a line for every home with the Hometype.

SELECT tblOwners.OwnerRecordName, tblHomes.HomeName, tblHomes.HomeType
FROM tblOwners INNER JOIN tblHomes ON tblOwners.OwnerID = tblHomes.HomeOwnerID
WHERE (((tblHomes.HomeType)=10));

I can’t figure out how to refine this query so that each owner with a home of the appropriate hometype is listed only once with no duplicates. The result will be passed to a mail merge generated from a selection criteria form (there will be other criteria) so the answer must be in SQL.

Reply | Quote

Replies

Try setting the Unique Values property of the query to Yes (click in an empty part of the query design window, then activate the Properties window). This is equivalent to adding the keyword DISTINCT after SELECT in the SQL:

SELECT DISTINCT tblOwners.OwnerRecordName, …

Reply | Quote

Hi Hans

Sorry, this doesn’t seem to make any difference. I still get multiple rows with the same owners, a row for every home they own with the appropriate hometype. E.g.

Forest Healthcare Sycamore House 10
Forest healthcare Ash House 10
Acorn Healthcare Acorn House 10
Acorn Healthcare Chestnut House 10
Acorn Healthcare Beech House 10

I need one line for Forest healthcare and one line for Acorn Healthcare.

Regards

Reply | Quote

Good Morning David…

These are distinct records…
The different house name makes them distinct…
If you want to see Forest Healthcare 10, Acorn Healthcare 10 (I’m assuming the 10 represents the house type), you need to remove the house name field…

HTH

Reply | Quote

Hello Trudi

This worked, thanks. Seems logical once one gets there.

Cheers

Reply | Quote