Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Unanswered: Need help with due date code/query

    I am working on an employee database (design features are listed below).

    We have various training courses that are required 30, 60, 90 days from date of hire.

    What I want to do is run 30, 60, 90 day queries or reports that will show me all employees missing the required training for those timeframes.

    Not sure how to do this.

    I also want to run something that will show me: of the total # of current employees, __ (# or %) have completed X course.

    Any help you can provide is appreciated.

    Thanks



    Employee Database Design

    CURRENT TABLES/FORMS:

    Employee Info:

    Last Name
    First Name
    Blue Sky Job Title
    Hire Date
    People First ID
    Affiliation (State, County, Other)
    City
    Zip
    Div Dir
    Supervisor

    Training

    Capture dates for all mandatory DOH training
    Other disaster preparedness training (ICS 200, 300, etc)

    Emergency Duty Assignment

    ICS Section
    Primary ED
    Secondary ED
    IMT member (yes/no)
    Reassignment info:
    o Exempt/LD
    o Comments

    Deployment

    Willing to deploy (yes/no)
    Deployment team (spns, epi, etc)

    Activation History

    Event name
    Assignment
    State Date
    End Date
    Comments


    REPORTS:

    Emergency duty assignments
    o By employee name
    o By ED
    o By ICS section

    Training
    o % completed mandatory
    All staff list
    By DD?
    By Supervisor?
    o Tickler for 30, 60, 90 day training requirements
    o NIMS compliance check (training vs ED assignment)


    OTHER:

    Mail merge/labels for bar codes

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well there is more then one way to approach this. fundamentally though it is impossible to query on something that isn't entered - - so given that - one approach would be to set up an AppendQuery that will add all the classes when you set up a new employee. But all those new training class records will have a blank in the ClassDate field to start.

    So now when you go to report - you can pull all training records that have a blank ClassDate.

    In your Classes table you probably want to add a field 'RequiredBy' and populate that with 30, 60, 90 as appropriate. This may help with sorting in some of your reports.

    Hope this helps.
    www CahabaData com

  3. #3
    Join Date
    Oct 2011
    Posts
    6
    Thank you for the reply. I am very new to building this kind of db.

    The training table I have right now has the following fields:

    Training ID (autonumber) - primary key
    employeeID (to link to the main table/form)
    ethics (date/time)
    diversity (date/time)
    etc (list of course names - all date/time to capture completion date)

    If I add the "required by" field to this table is it being added for each course or just one "required by" field for the entire table?

    Would I then do a calculation to add 30, 60, 90 days from the hire date? Note the "hire date" field is in a diff table - does that matter?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    actually a school is one of the classic db exercises that one often sees when studying database design. the proper design is to have a table for 'students' have a table for 'classes' and then have a table - which is called the transaction table generically - but in your case something like 'training'. In your case it would be employees rather than students but the idea is the same.

    [another classic example is a company with recurring customers. it has a 'products' table, a 'customer' table and then the transaction table typically called 'sales'.]

    In form view you would have your main form sourced on the employees table and select an employee - Jim . You would have a subform sourced on the Training table. when in the subform you would select a class - and this field would be a lookup field sourced from the classes table.

    You don't want to design with classes going left to right as columns - that will put you in a box as things change and make queries more difficult. It is call not normalized. You want to think thru the classic 3 table concept as this really holds the fundamental of the correct design for you.

    Hope this helps.
    www CahabaData com

  5. #5
    Join Date
    Oct 2011
    Posts
    6
    I think that makes sense to me. So the table for classes is simply a list of the course names that will be a drop down in the training form and in the training form is where I will enter the completion dates for each class.

    Is the training form where I would also put the "required by" date? If so, am I doing a calculation or expression or code in the form to get the 30, 60, 90 day date or is this done in a query?

  6. #6
    Join Date
    Oct 2011
    Posts
    6
    Is this correct?

    Create a Training table with the following fields:

    TrainingID (autonumber) - primary key
    ReportBasicUserID (number) - links to main employee table/form
    ClassID (number) - links to class listing table
    ClassName (lookup)
    ClassRequiredBy (date/time) - to calculate 30, 60, 90 day from HireDate
    ClassCompletion (date/time) - to be entered upon completion


    Create a Class table with the following fields:

    ClassID (autonumber)
    ClassourseName (text)

    I am very confused on how to link the hiredate (from employee table) to the requiredby date (from training table). I've attached what I've done so far. Is this close??

    Somehow I will need to figure out how to have all the mandatory training classes automatically add upon entering a new employee and then automatically enter the "requiredby" date in the next column. Is this possible?
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    I would put the 30,60,90 in the Class table as the 'RequiredBy' field. When you select the class you thereby select that number too - which you can use to add to the HiredDate value.

    You would be well served to browse around for an Access text book - easily found at Amazon or a large book store - - some of them besides having all the basic instruction may well have very similar examples to what you are doing.

    Hope this helps.
    www CahabaData com

Posting Permissions

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