Results 1 to 12 of 12
  1. #1
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23

    Query to count number of new customers per month

    Hi, I am new to the forum. Thanks in advance for your help.



    I have a table with a year's worth of data. I want to be able to create a report to display monthly the number of new customers. So typically it is a case of diminishing returns. I may have 100 new customers in Jan, some of those customers may return in Feb, but I only want to count the customers who are unique for February. I need to do this for the entire year. I thought of creating a new "unmatched" query for each month but there must be an easier way. Also thought of a crosstab query which could display the months easily.

    Any thoughts?

    Here is a sample of what my table looks like

    Customer DateAdded Current Status
    ABC 1/1/2010 Pending
    ABC 1/15/2010 New
    XYZ 1/30/2010 Pending
    XYZ 2/4/2010 New

    In this example, I would have one new customer in January, and one new customer in Feb. This is what I want to query, and eventually the report to display.

    Thanks!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    how about something like:
    Code:
    select month([DateAdded]) & "/" & year([DateAdded]) As MonthPeriod,
    
    count(customer) as NewCustomers
    
    from table
    
    group by month([DateAdded]) & "/" & year([DateAdded])
    
    having year(month([DateAdded]) & "/" & year([DateAdded])) = 2010 and [current status] = "new"

  3. #3
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    Adam,
    Thanks. I should have mentioned that in the table each customer may have several records each one reflecting one of the products they have selected. So, each customer appears several times in the table. I am therefore getting a result that counts each record, not each customer.

    Also, any thoughts on how I get Feb to show only those customers who are new, and were not accounted for in January. And for March to only show those who are new that were not counted in January or February, etc, etc.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    well you might have to do it in two queries Vicky. My example (which I hope works!) assumes that the "NEW" value in the status column indicates a new customer, which has not been seen in any of the previous months. We can't really help you fully unless we get a little more information.

    for instance, if ABC customer has more records after January, the task is more difficult because that means that the 'status' column really can't be used as a tool, which is one of the tools that i did use in my sql.

    the query, regardless of the situation, requires a grouping on the date field. and whether you use a domain aggregate in the sql or not, somehow you have to get the count of the unique customer records based on each one of those groups. Since I don't really understand the your data completely, I wouldn't know if it would require a criteria clause or not either.

    does that make sense?

  5. #5
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    The NEW refers indicates they have signed up for a course. Each customer may be repeated in this table because they make take several courses and each time they register for a new course, they are indicated as NEW (I know!!) They could also be pending, or completed. I am looking to capture those who are NEW only at this point. I have created a basic query to show only those who are in the NEW category and to remove fields that don't seem relevant.

    Yes, ABC customer can have more records after January. And they could be "NEW" multiple times depending on how many courses they signup for or attend.

    The date grouping you suggest works very well - Thank you. What I want to be able to capture is how many new customers there are in January - fairly easy in theory, because they would all be new at the beginning of the year. Problem is I am getting repeating data for the customers because they may be listed more than one time. Ultimately, I would like a count for February of how many unique customers there are that were not there in January, and so on for each month.

    By domain aggregate, do you mean DCount?

    Here's an better example of my data

    Customer CurrentStatus InternalCustomer DateAdded
    A&S Inc New American Learning 3/9/2010
    AS, L.L.C. New American Learning 8/5/2010
    AS, L.L.C. New American Learning 1/4/2010
    AS, L.L.C. New American Learning 8/11/2010
    AS, L.L.C. New American Learning 8/26/2010
    Associated Mortgage New American Learning 1/3/2010
    Associated Mortgage New American Learning 6/29/2010
    Associated Mortgage New American Learning 8/12/2010
    Associated Mortgage New American Learning 4/10/2010
    Associated Mortgage New American Learning 11/29/2010
    Blue Hill Inc New American Learning 1/6/2010
    BNA, Inc New American Learning 6/28/2010
    BNA, Inc New American Learning 9/29/2010
    BNA, Inc New American Learning 10/1/2010

    The result I am hoping to report on is

    Jan 3
    Feb 0
    Mar 1
    April 0 - because this customer (Associated Mortgager) was counted in January.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    there are a bunch of ways vicky, but explaining them all is not practical. my immediate thought would be to use these:

    DMIN()
    DISTINCT
    (2 queries needed)

    Complex Querying is not really my strong suit if I've had a few brews (), but I'll give it a shot. Here goes...

    1st set:
    Code:
    select distinct Customer, dateadded
    
    where dateadded = dmin("dateadded", "table", "[customer] = '" & [customer] & "'")
    2nd set:
    Code:
    select month([DateAdded]) & "/" & year([DateAdded]) As MonthPeriod,
    
    count(customer) as NewCustomers
    
    group by month([DateAdded]) & "/" & year([DateAdded])
    
    having year(month([DateAdded]) & "/" & year([DateAdded])) = 2010
    then of course, on a report, you'll have to fill in the missing time periods yourself with 0's or something. there is no way to do this through the query interface.

  7. #7
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    This looks really great. Is there anything in the code that you think would cause missing expression in query expression 'dateadded where dateadded = dmin("dateadded", "table", "[customer] = '" & [customer] & "'")

    Is the the matching " before the last customer? Tried that, but to no avail.
    Thanks!

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    did you add the FROM clauses to those statements? (laughing) I left those out. maybe on accident, maybe not. I'll let you decide.

    Code:
    select .....
    
    from table .....
    
    group by .......
    
    etc, etc...
    <EDIT>
    sorry! that doesn't make much sense does it? Here's the whole thing, if you didn't catch on:

    query 1 (name = query1):
    Code:
    select distinct Customer, dateadded
    
    from yourtable
    
    where dateadded = dmin("dateadded", "table", "[customer] = '" & [customer] & "'")
    2nd query:
    Code:
    select month([DateAdded]) & "/" & year([DateAdded]) As MonthPeriod,
    
    count(customer) as NewCustomers
    
    from query1
    
    group by month([DateAdded]) & "/" & year([DateAdded])
    
    having year(month([DateAdded]) & "/" & year([DateAdded])) = 2010

  9. #9
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    I was just doing that - Really !

    Its been years since I did any SQL code, so I am really rusty (creak, creak) but I did some searches for DISTINCT syntax and figured that the FROM clause was missing. Started trying to build the code from scratch; which is a great learning process.

    Thanks!

  10. #10
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    This looks amazing. Okay, I know I am on the verge of being a pain, but anyway to show months
    1/2010
    2/2010
    3/2010

    Instead of
    1/2010
    10/2010
    11/2010
    2/2010

  11. #11
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    not a pain. I wouldn't answer if I didn't enjoy telling you how to do this.

    at any rate, you want to sort the dates right? put an order by clause in:
    Code:
    select month([DateAdded]) & "/" & year([DateAdded]) As MonthPeriod,
    
    count(customer) as NewCustomers
    
    from query1
    
    group by month([DateAdded]) & "/" & year([DateAdded])
    
    having year(month([DateAdded]) & "/" & year([DateAdded])) = 2010
    
    order by month([DateAdded])
    I hope that'll work, but I'm not sure. it might throw an error and tell you the expr is not included in the group by clause. if it does, try:
    Code:
    month(month([DateAdded]) & "/" & year([DateAdded]))
    in the order by clause instead. the other alternative, which I never use, and I should because it's easier, is to use the FORMAT() function on the date field, which does the same thing as what I've given to you in 3 posts now. sorry about that, but both ways give the same result. mine is just more difficult to read, for reasons I will never know.

    this is obviously not my strong suit, these complex thingies, queries. if it was, the thread would've been solved 5 posts ago.

  12. #12
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    Thanks a bunch. It worked perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-26-2010, 02:11 PM
  2. Replies: 7
    Last Post: 07-22-2010, 01:14 PM
  3. Count number of identical posts
    By Patience in forum Access
    Replies: 1
    Last Post: 06-16-2010, 04:27 AM
  4. Individual weekday count in a month
    By Silver_A in forum Queries
    Replies: 6
    Last Post: 04-16-2010, 08:14 PM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums