Jan 4 2012, 03:51 PM
I hope I am asking this in the correct forum.
I have a table with Year, Account Number, Customer Name and Spend. There are 6 years of data in the table. I need to write a query or queries to tell how many new customers were added each year.
So, 2004 will have a baseline number of customers, then how may were added in 2005, 2006, etc. BUT, if a customer was included in 2004, then dropped off for 2005, then came back in 2006 they are not considered new.
Ocan get the unique customer number by year, but then I'm kind of lost.
Any help with his would be greatly appreciated!!! Thanks.
Jan 4 2012, 05:00 PM
If I'm reading your request properly, I think you'll need two Totals queries to accomplish this. First, you need a list of each account's initial year:
SELECT AccountNumber, Min(intYear) AS MinOfintYear
GROUP BY AccountNumber;
Save this query as "qryFirstYears". Then, create a second query that uses this one as it's data source:
SELECT Count(AccountNumber) AS CountOfAccountNumber, MinOfintYear
GROUP BY MinOfintYear;
This should list how many new accounts you got in each year.
Note that I'm using the field name "intYear" instead of "Year" - I would strongly recommend that you do the same, as "Year" is a reserved word in Access.
Hope this helps,