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.)