Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query for the first and last record (Access 2000)

    I need to build a query ,or better a report,showing the first and the last record under a given criteria.it is difficult for me even to project the design of such a query.Therefore i am sending the table.It consist of 2 fields Customerid and region.For example for region 1 the customerid ranges between 1 and 1679. is it possible to build a report showing the summed up results for the customers for the fist and last record? The report should look like this:

    Region Range of customers
    1 between 1 and 1679
    2 between 9000 and ....
    3 between ........

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

    Re: Query for the first and last record (Access 2000)

    You can use a Totals query for this. To change a query into a Totals query, click the Totals button on the toolbar or select View | Totals. In a Totals query, you can calculate the minimum and maximum values of a field. The SQL for the query is
    <code>
    SELECT a.Region, Min(a.Customerid) AS MinID, Max(a.Customerid) AS MaxID, "Between " & [MinID] & " and " & [MaxID] AS [Range of customers]
    FROM a
    GROUP BY a.Region;
    </code>
    See attachement.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for the first and last record (Access 2000)

    Thank you Hans for the wonderful,wonderful solution

Posting Permissions

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