Query Assistance (Access 97, sr2)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote