I have a query with the following data.
what I would like to have happen is for each year whare they have at least 40 points I would like to mark that year as a vesting year.
if they have less than 40 I would like that to be a non-vesting year.
if they have 4 non vesting years I would like to mark all their records as forfeit. and last if they have 10 vesting years to mark their main record as vested.
one last thing. the non-vesting dosn't start till you have a vested year. so in this example the 33 would be ignored and the counting would start with the 42

is it posable to do this with querys and update queries. or would i need to use vba code?
Trainer Code Year SumOfPoint
83 2002 33
83 2003 42
83 2004 54
83 2005 59
83 2006 98
83 2007 104
83 2008 12

Do you have the table structure behind this? I would think that with this sort of function you would want to have a table to hold this information. If you have a Trainer table, it would look like this:

tblTrainer
TrainerID - PK
LName
FName
...
Vested - Y/N

tblVesting
VestID - PK
TrainerID - FK
Year - Text
Points - Number
Vested - Y/N

Then I would store this data permanently instead of using a query to present the data. Can you post the structure?

HTH

Oh, anyway, you can do this with a series of queries, or with code.

mmcdonal

Look it up at: http://wrox.books24x7.com

I do have a main member database behind this and I would like to write back some information back to this database.
I have a memberID field, last name, first name, vested status, vested start year, and other fields.

I am trying to break my task up into manageable parts and I’m thinking what I can do is this.

I will add another filter in database to only show 'NV' records (not vested)
have the user type in the current year (they would enter 2008 when its jan 2009)
load up query
look up vested start year from main database.
if there is a start year jump to that year for current memberID.
run from vested start year (or first year in data if blank) to current year (user typed in)
Count records in 2 categories (less than 40 and greater than 39)
also write the year of the first greater than 39 record to vested start year in the main database
if there are 4 records less than 40 then change vested status in main database to 'FT' (forfeit) and also change vested start year to current year+1
if years greater than 39 is 10 or more than change vested status to 'VP'



this would be the first part in what I need to do. I don't think i can do this with queries and will need VBA code. I have just used macro's and queries to get to where I am now so would need help if VBA is needed.
Michael

You could automate this using code.

The NV "filter" sounds like you could use a query.
   SELECT * FROM tblMember WHERE [vested status] = No

The current year (last year) can be taken automatically from the system date: iCurYear = DatePart("yyyy", Date()) - 1

I am not sure of the other steps you want to take. I know you can automate all of this.

Can you send a copy of the tables, plus post how the data would look before the steps, and how it would look after? You can have this code run everytime the database is opened (check for data or state first) and totally automate it rather than having to click a button.

mmcdonal

Look it up at: http://wrox.books24x7.com