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.

dej090233,

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
FROM tblTestData
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
FROM qryFirstYears
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,

Dennis