Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: Updating rotating dates

    I am using MS Access 2002.
    I am creating a database with tables something like this.

    I created the table:
    tblemployees
    emp_initials last_training_date
    qv 02/18/04
    rv 08/31/04

    I created the query and added the next_training_date by adding DateAdd("m",6,[last_training_date]).
    qryemployees
    emp_initials last_training_date next_training_date
    qv 06/18/04 12/18/04
    rv 08/25/04 02/25/05

    What I need to happen is that every time I enter a knew last training date it becomes the new next-training-date and drops or appears to drop the old date on the form I am going to create for the users to see. Using the "qryemployees" example above for emp_initials "qv", a user enters for "qv" the date which in this case was earlier instead of waiting until 12/18/04 the training was done on 12/04/04 so the new next_training_date is 06/04/05, so I need 12/18/04 to be dropped from view, but still would be available as to prove the training was done. Of course there will be other columns but the trainining_date columns would be constantly rotating by adding 6 months to the last training_date. I hope I have made my self clear. Can someone help me?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try a table that hold initials and training date (NOT last training date)

    from there, you can query up a list of employees and most recent training dates (hit the sigma in the query design window, group by initials, max the date)

    ...from there, your dateadd is +6 on the most recent training date gives you the next training date, and all trainings are saved until your disk crashes.

    izy


    (i don't like initials... why not an autonumber primary key on a decent table of employee names - just in case you end up with Felicity Smith & Fred Spatula as employees)
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •