Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries on One to Many Tables (All)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries on One to Many Tables (All)

    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, ...

  3. #3
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries on One to Many Tables (All)

    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

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries on One to Many Tables (All)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries on One to Many Tables (All)

    Hello Trudi

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

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •