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

    Getting the Max record -1 (Access03)

    A while back Hans walked me through getting the last record of a group. I now need to retrieve the record just prior to the Max record.

    Situation: Tracking facts about job history. For example the different postions a person may hold during a career. The first request was to just return the last/current position. Now I need to know what position they left to assume the current position.

    Thank you

    Fay

    Hans instruction to return the Max or current position was as follows:

    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

    MaxDate: 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.

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

    Re: Getting the Max record -1 (Access03)

    This is going to take an extra query.

    1. You already have this one, but for completeness sake I mention it again. 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 MaxDate: 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 a line from the ID field to the ID field. Add the ID field to the query grid, then add the date field from the original query twice. Change the query to a Totals query. Leave the Total option for the ID field as Group By, change it for the first instance of the date field to Max and for the second instance of the date field to Where. This will automatically clear the Show check box. Enter the following in the Criteria row for this column:
    <[MaxDate]
    Insert LastButOne: before the name of the date field in the middle column. This query will return the next to last date for each person. Close and save this query.

    3. Create a new query, and add both the original query 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 LastButOne. Add all fields from the original query to the query grid. This query will return information about the most next to last position a person held (test this by switching to datasheet view). Close and save the query.

Posting Permissions

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