Understanding why my query cannot be edited

[Trystyn]

I haven't created a database for a long time and perhaps the reason for this problem is very obvious and easy but I am struggling to get my head around why one of my queries is working as intended and the other isn't.

I have essentially 3 tables:

1) EmployeeList, with fields: EmployeeID, Forename, Surname, Fullname (Calculated from previous 2 fields)


2) JobTitles with fields: TitleID, Title, Department


3) TrainingProcedures: ProcedureID, ProcedureNo, ProcedureName


Now I want to link the Job Titles with the Procedures which those Jobs need to be trained on. To do this, I have created a new table LinkTitleToProcedure and made a query using this with table 2 and 3.


This worked as intended but only when I changed the primary key of TrainingProcedures from ProcedureID to ProcedureNo.


I can now link any job description to the procedures.


Unfortunately, after this I come unstuck. I need to be able to select Employee1 and tell the database that Employee1 is a QA technician for example, and then the database can tell me what training procedures Employee1 needs. This needs to be editable so that I can add dates for training and refresher training but no amount of tinkering with different ways of linking the tables is producing an editable query to this effect.


What am I missing?


Many thanks for your time

 

[CJ_London]

I fail to see how you are linking your tables - how do you know which training a QA technician needs? - I would expect to see another table linking training procudures to job titles.

Similarly I would also expect to see a table that lists alll the training completed for each employee under each job title he/she has had

 

[Trystyn]

I fail to see how you are linking your tables - how do you know which training a QA technician needs? - I would expect to see another table linking training procudures to job titles.

Thanks for your response. The three tables listed are to my mind the raw data. I have stated just below this that there is a link table LinkTitleToProcedure.

In order to make this work I had to change the primary key of the TrainingProcedures table from the ProcedureID autonumber, to the field ProcedureNo to make the query editable. I do not know why this worked, and I've not been able to make equivalent changes to get the link between EmployeesList and JobTitles to work properly.

Similarly I would also expect to see a table that lists alll the training completed for each employee under each job title he/she has had

There will be fields for TrainingDate, Trainer, RefresherDate or somesuch but I haven't created this yet. I'm having enough problems linking the ones I have.

 

[CJ_London]

I had to change the primary key of the TrainingProcedures table from the ProcedureID autonumber, to the field ProcedureNo to make the query editable

Not sure why, unfortunately you are not providing sufficient information to suggest why this may be happening or a solution.

Why don't you post a screenshot of your relationships plus details of each table design (field name, field type)

 

[Trystyn]

Been back on this again and hopefully with the images attached I can explain a little better.


Title to procedure.jpg shows a working query where I can update which procedures are required for which job titles.


Similarly, employee to title.jpg shows the query where I am able to give each employee any job titles which have been held.


The final image, full.jpg shows the full relationships between the tables, and from this I obtain a list of employees with all titles held, and all procedures which they require training on. This query is not editable, to I am unable to use it to link to a new table with training dates/refresher training etc.


I have also tried linking the two working queries together across the title field to produce exactly the same result.


I appreciate your on going help with this and hope it's a little clearer.


Thanks.

 

[CJ_London]

I think you are thinking like an Excel developer

You might get this to work by changing the recorset type in your form to dynaset - inconsistent updates but you'll need a large amount of code behind the update to ensure all links are preserved.

Having done a pretty good job of creating separate tables and normalising your data structure, you are then stitching it back together into a single view. You would be better to look at your form design and break it down into a main form and subforms.

For example:
The main form would have the employee list as its record source, you would then have a subform with linktitles and job titles as its source and linkchild and linkmaster set to employee ID

Within that subform you can have two further subforms - one for the training that relates to the title, and one that relates to the title and employee (at the moment you do not have a table for the latter) but it would be something like this

tblLinkEmployeesToTrainingCompleted
ID Autonumber PK
EmployeeID Long FK
ProcedureNumber Long FK

A few comments about your table structure.

If Title is text, what happens if you have two job titles which are the same, but in different departments?

I presume current position in LinkTitlesToEmployees is a boolean set to true for the current position and false for everything else. You would be better to have a date field (called say DateFrom) which is populated with the date the employee started that role - the current one is then the record with the latest date. If the employee leaves, you can set it to null

It would be good practice to include an autonumber PK for your two link.. tables

 

[Trystyn]

Sorry for the delay in posting, this has had to take a back seat for a while.


I have taken your advice, the DB is not quite everything I'd envisaged from the outset as doing it this way, I have to input each new training procedure for each employee to build the data.