Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    limiting return from query on a report (Access03)

    A person may over a career have multiple postions in an organization. I want to track all of them, but in the report I want to report only the last position. The date, name, and department is in the query. Date sorted in descending order. How do I restrict the return to just the last department for the person?

    Thanks

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

    Re: limiting return from query on a report (Access03)

    0. The sort order of the query is irrelevant, for reports ignore the sort order in the record source. You might as well remove the sort order.

    1. Create a query based on the query you have. Add only the field identifying the person (the "ID field") and the date field. Click the Totals button on the toolbar or select View | Totals.
    Leave the Total option for the ID field as Group By and change the Total option for the date field to Max. Insert
    <code>
    MaxDate:</code>

    before the field name in the date field column. This query will return the most recent date for each person (test this by swtiching to datasheet view). Close and save this query.

    2. Create a new query, and add both the query you already had and the one you just created. Join them by two lines: one from the ID field to the ID field, and one from the date field to MaxDate. Add all fields from the original query to the query grid. This query will return information about the most recent position a person held (test this by switching to datasheet view). Close and save the query.

    3. Set the record source of the report to the last query. Specify the sort order in the Sorting and Grouping window.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: limiting return from query on a report (Access03)

    Thanks Hans. I will work with this this evening. I appreciate the information. Fay

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: limiting return from query on a report (Access03)

    Thank you Hans. I really appreciate you knowledge and support. I have never had to do this. It worked just as you said it would. Fay

Posting Permissions

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