2 table query problem

[Meanwhile]

I need help. I have tried all types of queries to no avail.
I have 2 tables

table Activity - contains training codes with dates that employees have trained on

fields: EmpID, EmpJobCode, TrainingCode, DateTrained


table ReqTraining - contains all training codes required per job code

fields: EmpJobCode, TrainingCode

I want a listing of all required training codes for a single job code and I want the date trained if that training code is in the Activity table.

Sounds simple but GRRRRR....

THanks in advance if someone can help me

 

[mwn1218]

A question first:

How are you realting the two tables together?
What are you trying to do with this query (use it in a form/report)?

 

[Meanwhile]

I have related the tables by Job Code.
Query To include all records from tblRequiredTRaining. I want a list of all required training codes for that job code and a date trained from the activity table if that training code was completed. Hope this clears it up

Thanks again

 

[wazz]

It's a bit hard to decipher what you have. It looks to me like you need more tables. Do you have more tables already? (Ex: tblEmployee, tblJob, tblTraining, plus composite tables tblEmployeeJob, tblJobTraining and tblEmployeeTraining).

This tblTraining table holds the 'course' data:
tblTraining
TrainingCode (PK)
TrainingDesc

This tblEmployeeTraining table combines employees and their training:
tblEmployeeTraining
EmployeeTrainingID (PK)
EmpID (FK)
TrainingCode (FK)
TrainingDate

This tblJobTraining table combines the Job data (tblJob) and the required training data:
tblJobTraining
JobTrainingID (PK)
JobCode (FK)
TrainingCode (FK)

I think you want this:

(For each)
JOB ___________

(Show)
Training Code ___
Emp/Date Trained
x/__________
y/__________
z/__________

Training Code ___
Emp/Date Trained
x/__________
y/__________
z/__________

Correct? I better stop here.

 

[Meanwhile]

Actually there are 3 tables

tblActivity
EmplyeeID
TrainingCode
DateTRained
JobCode

tblEmployees
EmployeeID
JobCode
FirstName
LastName

tblReqTraining
JobCode
TrainingCode


I want a list of all Required Training per EmployeeID,
and if the TrainingCode is in the tblActivity, then list the DateTRained for that TrainingCode. Yes I think that all 3 tables are needed for this query. Can it be done? I am at a loss.

Thanks again

 

[maikon]

Please see it:

https://www.access-programmers.co.uk/forums.xf/index.php?posts/354939/

 

[wazz]

Do you mean you have 3 tables in the query or 3 tables in total? I see you have JobCode in all 3 tables. that's the first sign of trouble if you don't have any more tables. you should have a Job table that stores the JobCode and JobName. do you have that?

(You should also have a Training table, it seems. Keep the Training and the Job separate. Then combine the two in yet another table as necessary, ie. Job x requires Training x, y, and Job y requires Training x and z, etc.)

 

[Pat Hartman]

1. Open the QBE grid and add the employee, rec training, and activity tables in that order.
2. Join the employee to rec training on JobCode.
3. Join rec training to Activity on TrainingCode and change the join type to Left.
4. Select the columns you need from each table.

You will end up with a list of employees, their required training, and the date for any completed training.