Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: Training Database

    Hi guys,

    Lab staffs are required to undergo 3 day training course within every 3 year period. The course options are 1 day course, 2 day course and 3 day course.

    The course can be taken as:

    3 continues days within 3 years
    1 day course taken 3 times individually anytime within 3 years
    2 continues days (meaning 1 more day required within 3 years to complete training)

    The 3 year period starts again from the date when 3 days have been completed.

    I.e. if the staff has completed 2 individual days in the 1st year than that mean 1 more day is required within the last 2 years to complete the training. However if the staff instead does a 2 continues days in the last 2 years then the 1st day of 2 continues days will complete course requirements and renew the 3 year period.

    I have done the basics (see attachment, tables, relationship, forms) and now I need to implement the above feature but not sure how I am going to achieve this. I was thinking of creating a report that would list all staff that requires training or is expiring in near future. I would like to print letters to those labs where their staffs have failed to meet the training requirements.

    Can someone give me some guidelines/hints/tips on how to achieve the above.

    Sorry if this is confusing or if I haven’t explained it properly.

    Thanks
    Attached Thumbnails Attached Thumbnails Training Database.jpg  

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How about dropping another table for course_dates instead of one static course? It is a little bloat schema-wise I know, but given the intent of the database, I think it's a fair compromise.

    Anywho, with this method, you would then have individual entries for every date that a given person is in a class, regardless of whether they're continious days or not. Then you can pull the date of the last entry for any given person, extend backwards three years and pull a sum of how many classes are attended within that period.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Posts
    5
    Hey Teddy..... thanks for the reply......so you think I should instead make a new table for course date.....if I do that then which table do I connect it to (Relationship) TrainingLogDetails or CourseDetails.....

    Sorry about the dumb question as may have guessed now.....I'm new to Access

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Training log details. This new table would be linked by the individual training session. Linking it to classes would mean that each type of class would occur during the same dates, eg: all "type 3" courses would occur from jan 1 - jan 3.

    The idea is to break out the actual dates for each training session to make it easier to search on. If this were an enterprise level application on an enterprise level platform, I would probably create this new dates table as a temporary recordset at runtime. Since this is an ad hoc app, I think you can get away with the additional physical table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2004
    Posts
    5
    See the attachment, I hope I've got this right. So Teddy how would I go about doing the below:

    "pull the date of the last entry for any given person, extend backwards three years and pull a sum of how many classes are attended within that period."

    Thanks
    Attached Thumbnails Attached Thumbnails Training Database2.jpg  

  6. #6
    Join Date
    Sep 2004
    Posts
    5
    Another attempt to explain....incase if I didnt make sense

    It is a single course which is duration of 3 days.....staff dont have to do 3 continues days to complete course instead they have the option of spreading the days like: 1 day, 2 continues days, 3 continues days. It doesnt matter which way they decide to complete training as long as they have done 3 full days within 3 years.

    Ie.

    1yr....................2yr....................3yr

    1day.................1day.................1day - training complete

    3 days - training complete in 1yr so new 3 yr period starts from last day

    1day.................2day - training complete in the 2nd yr

    2day.................2day - [I]training complete, done 4 days so 3rd is where the next 3 yr period starts again and the extra day done counts towards this new period.

    I hope this is bit more clearer....sorry

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Del_Piero_3
    See the attachment, I hope I've got this right. So Teddy how would I go about doing the below:

    "pull the date of the last entry for any given person, extend backwards three years and pull a sum of how many classes are attended within that period."

    Thanks
    Ok, the easiest way to do this en masse would be to create a query that attaches a StaffID to each entry in CourseDate. Again, on an enterprise level platform you probably wouldn't have to do this intermediate step, but what the hell...

    qryCourseDates:
    -----------------
    SELECT TrainingSession, CourseDate, StaffID
    FROM (CourseDate INNER JOIN TrainingLogDetails ON CourseDate.TrainingSession = TrainingLogDetails) INNER JOIN StaffDetails ON StaffDetails.StaffID = TrainingLogDetails.StaffID

    Then:

    SELECT Title, Forename, Surname, JobTitle, COUNT(CourseDate)
    FROM StaffDetails INNER JOIN qryCourseDates ON qryCourseDates.StaffID = StaffDetails.StaffID
    WHERE CourseDate BETWEEN DMAX("CourseDate", "qryCourseDates", "StaffID = " & StaffID) AND DMAX("CourseDate", "qryCourseDates", "StaffID = " & StaffID)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Sep 2004
    Posts
    5
    I created a query called qryCourseDates:
    SELECT TrainingSession, CourseDate, TrainingLogDetails.StaffID
    FROM (CourseDate INNER JOIN TrainingLogDetails ON CourseDate.TrainingSession=TrainingLogDetails.TrainingLogID) INNER JOIN StaffDetails ON StaffDetails.StaffID=TrainingLogDetails.StaffID;

    I was getting a error message "Join expression not supported" so I made amendments which are in bold....

    Then I created another query called qryStaffTrainingExpiry:
    SELECT Title, Forename, Surname, JobTitle, COUNT(CourseDate)
    FROM StaffDetails INNER JOIN qryCourseDates ON qryCourseDates.StaffID = StaffDetails.StaffID
    WHERE CourseDate BETWEEN DMAX("CourseDate", "qryCourseDates", "StaffID = " & StaffDetails.StaffID) AND DMAX("CourseDate", "qryCourseDates", "StaffID = " & StaffDetails.StaffID)

    Before I made the bold changes in this query I got this message: The specified field <'StaffID'> could refer to more than one table listed in the FROM clause of your SQL statement.

    Now I get this message after making the bold changes: You tried to execute a query that does not include the specified expression <'Title'> as part of an aggregate function.

    Sorry about this Teddy....am pretty sure you've got better things to do.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You ahve to throw a group by clause on there to.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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