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 design (Access 2007)

    We have a table including fields for Order no., Order date, and Client no. Is it possible to create a "client retention rate" query that will indicate the percentage of clients with one or more orders in a specified month (e.g., Feb2008) who also had one or more orders within the previous 12 months (e.g., Feb 2007 - Jan 2008)?

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

    Re: Query design (Access 2007)

    You'd need to create 3 queries:
    1) A query based on the table that returns the unique clients with an order in the specified month.
    2) A query based on the table that returns the unique clients with an order in the year previous to the specified month.
    3) A query based on the two above queries, with a left join on the client id field that computes the count of records from the second query divided by the count of records from the first one.

    In the attached sample database, you need to enter the first day of the specified month, e.g. 2/1/2008 (US format) for February 2008.
    In a "real" database you'd probably want to specify the month on a form.
    Attached Files Attached Files

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

    Re: Query design (Access 2007)

    Thanks for this, Hans.

    1. My "last year" query works fine, but my "month" query lists nothing. I note that my order date field is a "datetime" type, so the expression "[OrderDate]-Day([OrderDate])+1" needs to exclude the time part of the data somehow?

    2. I was hoping to use characters like spaces, hyphens, slashes and brackets in the query names, and I tried this with quotation marks; e.g., qry'Client - Yearly Retention Rate 1/3 (LY)', but the query names are referenced in the Retention query and this doesn't work. Is it possible to use these characters in the query names somehow?

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

    Re: Query design (Access 2007)

    1. Try Int([OrderDate]-Day([OrderDate])+1)

    2. I never use spaces or punctuation in the names of database objects, fields or controls. It makes things much more difficult.

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

    Re: Query design (Access 2007)

    Int([OrderDate]-Day([OrderDate])+1) produces "ODBC - call failed".

    I note that "[OrderDate]-Day([OrderDate])+1" produces results like "1/02/2008 11:56:11 PM", but presumably this needs to be just "1/02/2008"?

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

    Re: Query design (Access 2007)

    ODBC - is the table linked to an external database? If so, check the documentation for the source database.

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

    Re: Query design (Access 2007)

    Yes, the table is in a linked SQL database, but there are dozens of similar tables and queries and that error doesn't occur elsewhere. What effect should the Int (Integer?) function have on the datetime field, which contains data like "1/02/2008 11:56:11 PM". Does it need a function to extract the date part?

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

    Re: Query design (Access 2007)

    SQL Server stores date/time values in a different way than Access. Does this work?

    DateSerial(Year([OrderDate]),Month([OrderDate]),1)

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

    Re: Query design (Access 2007)

    Yes, that date calculation works, thanks.

    However, the Retention query just displays parameter prompts for [qryLastYear].[ClientId] and [qryMonth].[ClientId], but I can't see why. I tried replacing the single count/calculation field with two separate count fields and a third calculation field and that seems to work though.

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

    Re: Query design (Access 2007)

    If you get parameter prompts, a name is probably misspelled. But apparently you've solved the problem, that's great.

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

    Re: Query design (Access 2007)

    If X% of clients in the specified month have also placed orders within the previous 12 months, then the rest must be either new clients or returning clients who last placed orders more than 12 months ago. How would the queries need to be modified to calculate the percentage of clients who last placed orders more than 12 months ago (i.e., *not* within the last 12 months)?

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

    Re: Query design (Access 2007)

    Create a copy of qryLastYear, and change the criteria

    Between DateAdd("yyyy",-1,[Enter 1st of month]) And DateAdd("d",-1,[Enter 1st of month])

    to

    < DateAdd("yyyy",-1,[Enter 1st of month])

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

    Re: Query design (Access 2007)

    Ah yes, thanks.

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

    Re: Query design (Access 2007)

    Ah no ... that calculates the percentage of clients who placed an order in the specified month and *also* placed orders more than 12 months ago, but some of these may have *also* placed orders within the last 12 months. I meant the percentage only of clients who *last* placed orders more than 12 months ago (but they did not place any orders within the last 12 months).

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

    Re: Query design (Access 2007)

    You need to create a series of additional queries for this - see the attached version. I only created the select queries, not the calculations.
    Attached Files Attached Files

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
  •