Query Assistance

[DeborahP]

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

 

[Rob.Mills]

Uhhh... I don't totally understand what you're trying to accomplish. Lots of detail in your explanation. Hard to follow.

Do you just need to add records to this table? Say you want to filter out all the records for people who have an O and no other records? Am I correct in my assumption?

 

[DeborahP]

Yes.

I need to find only the people with an O and no other record and then add a new record for this training. It will be classified as AT. Eventually, a person who has worked a year will have 3 records - 1 for new employee Orientation (will always be there and will have hire date as date completed training), 2 - Annual Training class and 3 - Annual telephone survey.

This corporate class is going to replace our AT for this year and everybody went to class so I need to update my database to reflect this class. I was able to generate an update query to find the AT record and change the date to the completed date for the corporate class. I now need to add the AT for people who do not have an AT out there.

Does this help???

Deborah

 

[Rob.Mills]

Ok. First you need to setup a query to filter. In the criteria for the name of the person use a subquery. It should look something like:

In (SELECT [Name field] FROM [Table Name] AS Expr1 GROUP BY [Name Field] HAVING Count(*)=1)


Then the criteria for the type of class should be 'O' of course. This will return all records with an O and only have one record.

Then you can base an append query off of this. To append the name of each record in your first query and AT in the other field.