Join Query?

[Aoife]

Hello all!

Have set up a database as per one jdraw's recommendations in a previous post (see attached), I want to be able to query both 'EmployeeHasTraining' details at the same time as 'PostionTrainingRequirements'.

In other words, how to show training completed to date plus what further training is required to fill the relevant job positions.

Is this possible at query level?

Regards
A

 

[namliam]

I would think so by joining EmpOccupiesPosition, PositionTrainingReqID, EmployeeHasTraining (outer joined) you should be able to see 1) the training required and 2) If that training has been done

 

[Aoife]

Hi
Thank you for the advice.

I created a query (Query1) based on tables tblEmpOccupiesPosition and tblPositionTrainingRequirements joined on JobpositionID.

In order to avoid an ambiguous outer join by adding the EmployeeHasTraining table to Query1, I created Query 2.

Query2 contains Query1 and EmployeeHasTraining table, outer joined on EmployeeID and TrainingCourse ID. This gives me the required information but unfortunately the ‘recordset is not updateable’.

Any further help would be fantastic as I’ve been attempting to sort for several days?:banghead:

 

[jdraw]

Aoife,

I created that model in answer to a poster who was having difficulty with tables and relationships. I did not create a database.

If you zip your database and post it here, I'll look at it.

In overview, if you take specific Employee who is occupying a Position, you can see if the Training required for the Position has been taken by the Employee. I think a query as outlined by namliam should work.

 

[Aoife]

Hiya, have attached the database. I'd like to be able to update the 'CompletedDate' in the 'PositionReqsEmployeeTrainingQuery2' if possible.

Thank you

 

[jdraw]

I'm not following your query. I'm looking at PositionReqsEmployeeTrainingQuery1 and Employee can have more than 1 Position?? Employee 2 occupies Positions 1, 2, 3???

I'm looking to see:
if Employee 2 occupies Position 1, then what Training is required for Position 1 that Employee 2 does not yet have? Or, in other words, what additional Training does Employee 2 need in order to satisfy the requirements of Position1?

Your data has only 1 training course for Employee 2.
Also for Position 1 there is only 1 TrainingCourse required CourseId 1.

Can you describe what question you are trying to find an answer to?

 

[Geotch]

If you want to update dates, don't think you'll be able to do it in a query, sounds like you need a main form for employee, sub form for entering training needed and another sub form for updating the training received.

I've update the database with three queries (Geof_query 1,2 and 3) that show who needs which training and which training is complete. You could put this on a single form and then use VBA or Macros to update the tables. Let me know if you need an example and I could upload a sample.

The query PositionReqsEmployeeTrainingQuery2 is missing details of any employee that hasn't been assigned training, otherwise it's the result as what I got.

Hope that helps.

 

[jdraw]

I agree with Geotch that a form with some controls is probably the best way to do entry and updates.
You could have a list of people with a subform of their Training; and a list of Positions with a subform of the Training for Position. When you attempt to move a Person to a Position, you could get a list of Training needed that the person doesn't have. In real life conditions you could see which Employee has (or has most of) the Training required for some Position.

You could have a combo of Employees and a combo of Training; make selections from each list to Update EmployeeHasTraining, and add the details Completed.

 

[Aoife]

Thank you, developed a listbox containing contents of PositionReqsEmployeeTrainingQuery2 so that a double click and the employee's training records appears in an editable, filtered form