General query

[circles]

Below is an example of a table I have built and it contains customers identified with a number and by name and the year they have been a customer. I am trying to develope a query that will identify who has been a customer for the last three years, two years, etc in each and every year. For example if I want to look up who has been a customer each year for the last three years the query should come up with Tom. Or would it be easier to build seperate tables for each year? The table I am working with has 1065 records and goes back to 1996. Any help would be appreciated.

Cust Name Year
1 tom 2001
2 bill 2001
3 mark 2001
1 tom 2000
3 mark 2000
5 ryan 2000
1 tom 1999
2 bill 1999
6 aaron 1999

 

[Frink]

Hallo,

It depends what you want. Do you want to know the first year they became a customer, or the total number of years they have been a customer, or how many years they have been a customer for, without missing a year?
Some are simpler than others.

To select the year they first became a customer, use
a query, grouped by customer number, and use the Min([Year]) function.

The way I would do anything more complicated would be to write a function to return the data you want for each customer.

- Frink