Full Version: Help - New Customers
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
dej090233
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.
doctor9
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.