Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 24th, 2008, 11:30 AM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
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

Reply With Quote
  #2 (permalink)  
Old March 25th, 2008, 06:37 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 25th, 2008, 09:21 AM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 25th, 2008, 10:47 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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. amit_mande@yahoo.com 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



All times are GMT -4. The time now is 07:45 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.