Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub form in Labels (Access 2003)

    I have a school database that includes family names, student names and class information. I need to generate labels for packets that have to go home, one per family. I want to include only one child's class on the label, so that the family does not receive more than one packet.
    How do I create a query to olny find one child name for each family name?
    I'm a novice access user, so I rely on the label wizard. I tried to create a query and then use it as a word mail merge source, but I got duplicate labels.
    Any help would be appreciated.
    thanks

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sub form in Labels (Access 2003)

    Try the following but substitute your own names:

    SELECT tblChildren.FamilyName, First(tblChildren.Childname) AS FirstOfChildname
    FROM tblChildren
    GROUP BY tblChildren.FamilyName;

  3. #3
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form in Labels (Access 2003)

    Thanks Pat. That sounds right. Remind me again where I do this? I use access about 2x a year, and by the time I'm done with a project I feel comfortable, but then forget until the next time!
    Thx

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

    Re: Sub form in Labels (Access 2003)

    Pat posted the SQL for a query. You can create it as follows:
    - Activate the Queries section of the database window.
    - Create a new query in design view.
    - Add the table containing the names. In Pat's example, it's called tblChildren, but in your database, it will probably have a different name.
    - Add the family name and child name fields to the query grid.
    - Select View | Totals to change the query into a Totals query.
    - Leave the Total option for the family name field as Group By (the default).
    - Change the Total option for the child name field to First (you can select it from the dropdown list in the Total row)
    - Switch to datasheet view to see the result.
    - If you switch to SQL view, you will see something resembling the SQL statement posted by Pat.

  5. #5
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form in Labels (Access 2003)

    Great.
    I was in the design view but was not familiar with the view totals.
    Thanks!

Posting Permissions

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