Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 24th, 2008, 11:30 AM
Authorized User
Join Date: Mar 2008
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default ticking off records with an update query

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

Old March 25th, 2008, 06:37 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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:

TrainerID - PK
Vested - Y/N

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?


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


Look it up at: http://wrox.books24x7.com
Old March 25th, 2008, 09:21 AM
Authorized User
Join Date: Mar 2008
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts

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.
Old March 25th, 2008, 10:47 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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.


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

Similar Threads
Thread Thread Starter Forum Replies Last Post
creating a ticking clock neddyoats Classic ASP Basics 13 February 5th, 2007 08:52 PM
I solved insert query.now see this Update Query. [email protected] VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Update Records bspeck Dreamweaver (all versions) 5 October 5th, 2004 03:05 PM
UPDATE RECORDS!!! a_pathak BOOK: Beginning ASP 3.0 1 March 3rd, 2004 04:00 AM
Help! Cannot update records mcalder Classic ASP Databases 6 August 14th, 2003 01:54 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.