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

    First record query (Access 2000)

    I have a table including the following fields.

    Order
    Client
    Date

    I need to create a query to count the number of new clients in each month. I can use a total query to group records by month, but how can I set the total parameter for the client field so that only the first order for each client is included?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: First record query (Access 2000)

    If you are <div style="width: 100%; background-color: #FFFFFF;">absolutely</div hiblock> sure that the lowest order# is the first order#, then you could use the Min function in the query. Otherwise, your first query (which returns Client, Order, and Date) needs to be joined to the original table on these 3 fields. The only problem will be if there is more than 1 order for a client in a specific day.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: First record query (Access 2000)

    Thanks for your reply, but I can't quite follow it. I created a totals query with "Group by" for the Order and Date fields, and "Min" (also tried "First") for the Client field, but it is including clients more than once.

    Example

    Data:
    Order, Client, Date
    1001, 301, 01May2006
    1002, 302, 01May2006
    1003, 303, 01May2006
    1004, 301, 31May2006
    1005, 301, 01Jun2006
    1006, 304, 01Jun2006
    1007, 305, 30Jun2006

    Required result:
    May2006, 3*
    Jun2006, 2

    *Client 301 has 3 orders (2 in May and 1 in Jun) but should be counted only once, for their first order on 1 May.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: First record query (Access 2000)

    Your post confused me, because you talked about only wanting to include the first order for each client for the month. I took this to mean you wanted to return the Order# of that order. Apparently, you just want to count the # of clients who had an order in a given month, correct? What does your SQL statement look like now?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: First record query (Access 2000)

    Yes, I want to count the number of *new* clients each month, i.e., I only want to count each client once ever (the first time they ever have an order). For example, if a client had several orders in every month from Jan 2006 onwards, then he should be counted only once, in Jan 2006.

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

    Re: First record query (Access 2000)

    This resembles the thread starting at <post:=540,508>post 540,508</post:>. Like there, you probably can't do this in one query. The attached database contains a series of queries to accomplish what you want.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: First record query (Access 2000)

    Ah, it is clearer now. It can be done in a single query, but requires use of a subquery. Something like this:

    SELECT Format([Date],"mmm/yyyy") as ReportingMonth, Count(*) as NewClient FROM yourTable _
    INNER JOIN (SELECT Client, MIN([Date]) as FirstDate FROM yourTable GROUP BY Client) as subQ ON subQ.client=yourtable.Client and subQ.FirstDate=yourTable.[Date]
    Group By Format([Date],"mmm/yyyy")

    I always have problems getting Group By to come out right for months, but this should get you into the ballpark.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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