Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Assistance (Access 97, sr2)

    Good Morning!

    I can not get my mind to work this morning and need some assistance from someone!!

    I have data from another database that I want to incorporate in my database. The other database (let's call it "A") has training information from all over the company. I track only certain information in my database (let's call it "B") . I want to take the people who took a certain class in the "A" database and give them credit in the "B" database. I teach all the required DHEC courses for my company and within the last few month we have trained all 4700 employees in a Corporate Integrity class. This can count as their yearly annual training for my records.

    I have already created an update query to change the date of the training from last year to this year if they had taken the Annual class last year. My problem is that I can not figure out how to add the people who did not have an annual class last year. These people would be celebrating a year of employee this year. They may have an "O" orientation class in database "B" but not an "AT" annual training. In reality everyone who has been here more than a year should have 3 classes in my database - New employee orientation, annual training class and annual telephone training. If the peson only has an O than I need to give them an AT with the date they attended the class.

    If you can understand this....please help!

    Thanks,
    Deborah

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Assistance (Access 97, sr2)

    Do you already have all 4700 employees in database "B" (perhaps with incomplete information), or have you only imported those who followed AT last year?

    If you don't have records for all employees, can you get the missing ones from database "A"? If not, from where?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Assistance (Access 97, sr2)

    Without table designs I can only give you some general advice. Assuming that you have a table that records the fact that person X took course Y, I believe what you want to do is use an Outer Join query, where you want to see all of the people who have been there at least one year, but don't have a record that they took course Y. The unmatched query wizard will help you create that kind of query, but the basic trick is to look for null values in the person ID field of the table with course taken records for course Y. Hope this makes sense - if not post some details on your table structure, and one of us will do our best to help you.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (Access 97, sr2)

    Yes, I have all the employees in my database. My database is solely for DHEC records. I maintain the database with weekly updates from payroll. When we have New employee Orientation on Monday, they come to class and then on Wed, I get a download from payroll. I search by Emp # and enter and O for training type and the date of Orientation. The following year, I produce a report to send to the director stating that your employee came to orientation on 3/1/2002 and it is time for them to come to Annual training (theyhave to come within their hire month). Once they come to AT, I go back to the database and search by Emp# and add a new entry for AT. Now, the next year 3/1/2004, I modify the old AT and change the date to the current year. They also have to complete a telephone training as well every year. An existing employee should always have 3 records, O for Orientation, AT for training Class and T for Telephone in my database. I am not concern with any other training. We offer all sorts of training. The other database keeps track of all the outside as well as inservices. This Corporate training was a requirement for all employees. We decided to count it as their AT as well as for inservice. So they will get credit twice for the same class.

    My database is very simple: Employee table linked to training table by Emp#. The training table consist of autonumber - training entry, Empl#, date completed, date entered (default of =now()), type of training (AT or T or 0).

    The other database consist of the same but location, instructor, hours, etc.

    I imported the table into my database. I created an updated query to update the AT completed date field to the date they completed the corporate training. So...my record could have stated AT on 6/6/2002 and now it states 1/14/2003. I ahve completed my AT for this year. It works with people who have an existing AT. My problem is I want to check to see if they DO not have an AT and then append the corporate as an AT with the date compeleted. I do not want to override the O record - I want to add a new record without manually doing this.

    How can I do this?

    Sorry so long ! Deborah

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Assistance (Access 97, sr2)

    So what you want to do is to add a new AT record for all those people who don't have an AT record, is this right?
    What you could do is to build 3 queries as follows:

    Query 1, selects distinct EmpNo from table tblTraining
    SELECT DISTINCT tblTraining.EmpNo
    FROM tblTraining;

    Query2, selects EmpNo

Posting Permissions

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