Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Elimanating Duplicates (97/SR2)

    I have a database for our Sunday School. I have one table for each family and another for each student, they are tied together by a Family ID#. I have students that go up through Grade 12.

    I want to print mailing labels for all the families that have students in Grade 6 and below. I am able to get the families that I want by use of a query but for each family that has more than one student in Grade 6 or below, I get an entry for each student. How do I narrow it down so that each family only shows up one time? (I tried changing the join properties but that didn't seem to help.)

    Thanks for any help.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Elimanating Duplicates (97/SR2)

    Hi Don,
    You ought to be able to do this either using a Totals query - use your normal link, only return the details from the family table and group on each field you use - or by using SELECT DISTINCT in your query rather than just SELECT. Both of these assume you're only returning data that is the same for each student in a family (eg address)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Elimanating Duplicates (97/SR2)

    I think you may still end up with duplicates if one family has multiple children in different grades. I'd tackle it in two steps rather than one: (1) Select the children in grade six and below making sure to include the field that has the family's ID. (2) Join Query1 to the Family table in a Totals query, bringing into the query grid only information about the family.

    Good luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Elimanating Duplicates (97/SR2)

    Rory,

    Thanks for the quick response. It probably does help, I'm just not getting it.

    In my query grid, I have:
    Family Name, Street Address, City, State, Zip from the Family table and Grade Level, criteria <7, from the Student Table.

    Here's the SQL, if that helps.

    SELECT [Family Information].[Last Name], [Family Information].City, [Family Information].State, [Family Information].Zip, Students.[Grade Level#]
    FROM [Family Information] INNER JOIN Students ON [Family Information].[Family #] = Students.[Family #]
    GROUP BY [Family Information].[Last Name], [Family Information].[Street Address], [Family Information].City, [Family Information].State, [Family Information].Zip, Students.[Grade Level#]
    HAVING (((Students.[Grade Level#])<7))
    ORDER BY [Family Information].[Last Name];

    When I View, Totals, I get a Group By under each item, however it still shows all the records. I'm not sure which one to change, or what to change it to. (As I think about it, what I want to view is only the unique addresses, not FamilyName, since I have a few with the same name, like Smith.) Hope you can make this simple. Thanks again for the assistance.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Elimanating Duplicates (97/SR2)

    Hi Don,
    Try changing the 'GroupBy' under Grade Level to 'Where' so that you don't actually return the grade - that is probably what is causing the multiple records to be returned.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Elimanating Duplicates (97/SR2)

    Rory,

    That did it!

    Now, just one more question. What did it do? I looked at the Help and that made it as clear as mud. I know that for long time users, this is probably pretty elementary but I was just wondering. Thank You so much for the help. This is the greatest resource out there.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Elimanating Duplicates (97/SR2)

    Don,
    By changing from a GroupBy to a Where, you stop the query from actually returning the grade in its recordset - it only uses it as a criterion (you'll notice that in the query design the 'Show' box becomes unchecked). This means that where you have a family with, for example, kids in 5th and 6th grade, your query originally would have returned 2 records because you were returning and grouping on the grade. So the records would have had all fields equal except grade. By changing it to a pure criterion, all fields are identical for both records and so the Grouping aggregates them into one record.
    I hope that made sense? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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