Query to count number of new customers per month

[vicky464]

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!

[ajetrumpet]

how about something like:

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"

[vicky464]

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.

[ajetrumpet]

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?

[vicky464]

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.

[ajetrumpet]

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:

select distinct Customer, dateadded

where dateadded = dmin("dateadded", "table", "[customer] = '" & [customer] & "'")

2nd set:

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.

[vicky464]

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!

[ajetrumpet]

did you add the FROM clauses to those statements? (laughing) I left those out. maybe on accident, maybe not. I'll let you decide.

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):

select distinct Customer, dateadded

from yourtable

where dateadded = dmin("dateadded", "table", "[customer] = '" & [customer] & "'")

2nd query:

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

[vicky464]

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!

[vicky464]

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

[ajetrumpet]

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:

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:

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.

[vicky464]

Thanks a bunch. It worked perfectly.