Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Date grouping/criteria (Access 2000)

    I have a table including the following fields.
    [Client#], [Order#], [OrderDate]

    I need to create a query that lists all orders for clients whose most recent order is more than 6 months old.

    For example, from the following records ...

    [Client#], [Order#], [OrderDate]
    100, 2001, 1 Jul 2005
    100, 3001, 1 Aug 2005
    100, 4001, 1 Sep 2005
    200, 2002, 1 Jul 2005
    200, 3002, 1 Aug 2005
    200, 4002, 1 May 2006

    ... the query should display the first three records (most recent order for client 100 is more than 180 days old) but not the last three (most recent order for client 200 is less than 180 days old).

    Is there a simple combination of grouping and criteria that can achieve this?

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

    Re: Date grouping/criteria (Access 2000)

    Create a query based on the table and add only the Client# and OrderDate fields.
    Change it to a Totals query.
    Set the Total option for OrderDate to Max
    In the Criteria line for the OrderDate field, enter

    <DateAdd("m",6,Date())

    Save this query.

    Next, create a query based on the table and on the query you just saved, joined on the Client# field.
    Add TableName.* to the query grid.

    Note: this query is not updatable.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date grouping/criteria (Access 2000)

    Thanks again, Hans. (However, because I need orders more than 6 months old, I changed the criterion to "<DateAdd("m",-6,Date())"; is that correct?)

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

    Re: Date grouping/criteria (Access 2000)

    Oops, yes, that is correct. Sorry about the mistake.

Posting Permissions

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