Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Query: first occurrence (Access 2000)

    I have a table with the following structure.

    [Order#], [Client#], [Date]
    1000, 234, 08/10/2005
    1001, 876, 21/11/2005
    1002, 456, 01/12/2005
    1003, 234, 06/12/2005

    I need to create a query that lists, for each month, the number of orders that were the client's first order, and the number of orders that were subsequent orders (i.e., they were not the client's first order), as follows.

    [Month], [First Orders], [Subsequent Orders]
    10/2005, 1,0
    11/2005, 1,0
    12/2005, 1,1

    I think this could be done by creating a select query to count first orders in each month, and another to count subsequent orders in each month, then joining these in a crosstab query by month. However, how can I determine whether an order is or is not the first for a client?

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

    Re: Query: first occurrence (Access 2000)

    You can use a crosstab query for this:

    TRANSFORM Nz(Count(*),0) AS V
    SELECT DateSerial(Year([OrderDate]),Month([OrderDate]),1) AS M
    FROM [tblOrders]
    GROUP BY DateSerial(Year([OrderDate]),Month([OrderDate]),1)
    PIVOT IIf(DCount("*","[tblOrders]","[ClientNum] = " & [ClientNum] & " AND [OrderDate] < #" & Format([OrderDate],"mm/dd/yyyy") & "#"),"Subsequent","First") & " orders"

    where:
    tblOrders is the name of the table
    ClientNum is the name of the client number field
    OrderDate is the name of the order date field

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

    Re: Query: first occurrence (Access 2000)

    Thanks once again, Hans.

    Only one initial hiccup: "Data mismatch in criteria expression". I'm wondering whether this is something to do with the order date field type (Date/Time)?

    I have a rough idea of what this query is doing, but if you could spare a few moments to explain briefly how it works, it would be much appreciated!

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: first occurrence (Access 2000)

    Is client number a text field?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Query: first occurrence (Access 2000)

    No, the client field is numeric.

    PS: It seems that the error is in the expression: "DateSerial(Year([OrderDate]),Month([OrderDate]),1) ". The OrderDate field is of t ype DateTime.

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

    Re: Query: first occurrence (Access 2000)

    It works without error messages for me. Do you have records with an empty OrderDate? That would cause problems.

    DateSerial(Year([OrderDate]),Month([OrderDate]),1) is the first day of the month of OrderDate, this is used to group the dates. You can set the Format property of this field to mm/yyyy.
    IIf(DCount("*","[tblOrders]","[ClientNum] = " & [ClientNum] & " AND [OrderDate] < #" & Format([OrderDate],"mm/dd/yyyy") & "#"),"Subsequent","First") & " orders" is an expression that calculates if it is the first order for a client, by counting all records with an earlier OrderDate.
    Nz(Count(*),0) is the number of records, the Nz converts missing values to 0.

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

    Re: Query: first occurrence (Access 2000)

    Yes, there are records with blank OrderDate fields (old orders that were cancelled before being processed, and new ones that haven't been processed yet). I imagine this means that if I create a query to list only orders with non-blank OrderDates and join it to this one, it should work? Or is there a way of allowing for blank dates within this query?

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

    Re: Query: first occurrence (Access 2000)

    One possibility is to create a query that selects only orders with non-blank OrderDates, and use that as basis for the crosstab query.
    Another possibility is to create a query that converts missing dates to a valid date that doesn't occur in the data, for example
    TheDate: Nz([OrderDate],#01/01/2099#)
    and use this query as basis for the crosstab query. All blank order dates will be grouped under 01/01/2099 (or whatever date you chose).

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

    Re: Query: first occurrence (Access 2000)

    OK, I've based this query on another query that lists only orders with non-blank OrderDates, and it works now, thanks.

    However, I may not have specified the requirements clearly. Here is an expanded example.

    Data:
    [Order#], [Client#], [Date]
    1001, 123, 01/10/2005 <-first-month (and first-ever)
    1002, 123, 02/10/2005 <-first-month
    1003, 123, 01/11/2005 <-subsequent-month
    1004, 456, 02/11/2005 <-first-month (and first-ever)
    1005, 123, 02/12/2005 <-subsequent-month
    1006, 123, 03/12/2005 <-subsequent-month
    1007, 456, 04/12/2005 <-subsequent-month
    1008, 789, 05/12/2005 <-first-month (and first-ever)

    The query should list, for each month, the number of "first-month" orders (those from clients who placed their *first-ever* order in that month), and the number of "subsequent-month" orders (those from clients who placed their *first-ever* order in a previous month), as follows.

    [Month], [First-month Orders], [Subsequent-month Orders]
    10/2005, 2, 0
    11/2005, 1, 1
    12/2005, 1, 3

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

    Re: Query: first occurrence (Access 2000)

    That's a quite different question. I think it'll have to be done with a series of queries. See the attached demo.

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

    Re: Query: first occurrence (Access 2000)

    Yes, that's it, thanks Hans. (Will I still need to use an additional source query to filter out records with blank OrderDates, to prevent mismatch errors, or is there something I can do within these queries?)

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

    Re: Query: first occurrence (Access 2000)

    As before, you must either filter out blank dates, or use Nz to replace them with a dummy date. The former seems more logical, since records without a date can't be classified as either first-month orders or subsequent-month orders.

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

    Re: Query: first occurrence (Access 2000)

    OK, thanks.

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

    Re: Query: first occurrence (Access 2000)

    I now have crosstab query as follows.

    [Month], [First-month Orders], [Subsequent-month Orders]
    10/2005, 2, 0
    11/2005, 1, 1
    12/2005, 1, 3

    Is it possible to include a monthly total of both order types, and the percentage of each type, as follows?

    [Month], [FM], [SM], [Total], [FM%], [SM%]
    10/2005, 2, 0, 2, 100%, 0%
    11/2005, 1, 1, 2, 50%, 50%
    12/2005, 1, 3, 4, 25%, 75%

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

    Re: Query: first occurrence (Access 2000)

    You can add the total in the crosstab query, as a row header.
    You can then create yet another query based on the crosstab query to calculate the percentages. This is done by dividing the First-month orders (or Subsequent-month orders) by Total, and formatting the column as a percentage.
    See attached version.

Page 1 of 2 12 LastLast

Posting Permissions

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