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.
I can 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,