1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 - time related query

Discussion in 'Business Applications' started by mikejreading, Aug 28, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Hi there,

    Can you give me some idea on how to do this...

    I am creating a Training Management solution. For this some of the training modules they need requalification after x time. (This time is stored in the database, but is currently stored as (6 months, annually or 3 years) (should this be stored as the number of days?).

    So the training table looks like this:

    TrainingCode - Primary Key
    Traning Name
    Requires refresher? - List (6 months, annually, 3 years)
    Mandatory? - Yes/No

    And I have another table which links a colleague to a training module with a date they trained which looks like this:

    ID - Primary Key
    Training Name - List from Traning table
    Colleague Name - List from Colleagues table
    Date Trained
    Trainer - List from Colleagues Table

    Nowww... what I can't get my head around is:

    a) How to create a report to show a list of all colleagues that are overdue requalification training

    b) How to create a report to show a list of all colleagues that are due refresher training in the next 60 days

    c) How to create the queries to run both of the above reports

    d) How to print a list of all mandatory training that is incomplete...
    That is a list of all colleagues mandatory training for what there is no record in the ColleagueTraining table.. I already have a list of all mandatory training linked to a job role in the ManTraining and Colleagues tables respectivley.

    In addition, I need to be able to limit the details put into the table by the following factors:
    - Colleagues should not be allowed to enter more than one training session, unless the session requires refresher training
    - Refresher training should be stored in addition to previous traning
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    As long as you do not need the Retraining to an exact day the 6 Month, Annual & 3 years is OK.
    But why not translate that in to a future Date?
    The queries that you require are reasonably straight forward, even more straight forward if you use the future date as suggested.
    I have a couple of reservations about your Table Collegue/Training design, you shouldn't store names in it just the TrainingID and the CollegueID.
    If you could create an Access 2000-2003 database and Import your tables (with some dummy data - no personal details) and then post a zipped copy on here I can assist you with the queries.
     
  3. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Hi,

    Attached is a copy of the file with various traning names in them. Just myself and a test colleague in the colleagues table.

    Feel free to play around with it, as I dare say it's not the best in the world.. im not a database guy haha, i can do the reports, so its just the backend im worried with

    Thanks :)
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Mike, I will take a look and get back to you tomorrow.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Mike, I have created 4 new Queries, the first returns the Last Date a person is Trained on a subject and is called "traincoll Lastdate".
    The others provide a list of Overdue Training (training Overdue), Training Required in the next 60 days (training Required) and last Mandatory Training Required (Mandatory).
    To demonstrate the last you have been promoted and have been trained for "Managing Store Food Safety".
    I am not sure about the other requests, perhaps you could explain a bit more about them.
    I have not modified any Relationships.
     

    Attached Files:

  6. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    OPB,

    Thanks for that!!!! It was the MAX function i was looking for!!

    The only other thing I am unsure of how to do is:

    My plan was for the table trainjob to link a job to required training. Training can either be mandatory or non mandatory, however needs to be assigned to specific job roles.

    Thanks for all your help OPB
     
  7. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Alsooo... a small flaw I've only just thought of.

    When you requalify for something (ie first aid - every 3 years), and you are overdue, then your name appears on the Overdue training query.. however, when you are retrained the old name still appears.. is there any method of archiving this information?

    Sorry.. I am such an access newb it is untrue :)
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you repost the database demonstrating what you mean by "when you are retrained the old name still appears".
    The trainjob is the right way to go, you should store the JobID and the trainingID in there.
     
  9. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Attached is an updated database showing what i mean.

    If you see the traincoll LastDate query.. you will see i refreshed my First Aid training sometime in 2009, however if you check the training Overdue query, you will see that my 2004 training still shows as being overdue.

    Thanks
     

    Attached Files:

  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I only get disability confident on the list.
     
  11. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Baically.. if you add me in the training table for having done a first aid course today, i will still appear on the Training Overdue query as the 2004 course has gone past the 3 years refresher boundary..
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You have already entered your First Aid training on the 30/08/2009 and First Aid doesn't come up on the overdue list.
    Perhaps when you checked it you hadn't closed the table to register the new date.
     
  13. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Sorry.. my mistake.. i think i was running the query without saving the table modifications :). Sorry.. as i said.. access newb :)
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    That is a little Access quirk that has caught me out dozens of times over the Years :D
     
  15. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Sorry.. final one OPB.

    Current plan of action is to create a query, which compiles everything that is required for a job role, and compares it against training an employee has taken, and then show me what is missing.. and hence show me what employee has training outstanding.

    Just to clarify mandatory training is required for health and safety, or legally.. however all training for a job role should be shown, whether mandatory or not
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/856404