'SQL query with composite key over multiple tables

I'm trying to do a database regarding employees' skills. Here's my Structure.

I'd like to make a query that shows, for each employee and skill, the actual and required level. The required level depends on the function each employee performs. I'm just starting with MS Access and I haven't found any similar example.

Thanks in advance.

sqlms-access


Solution 1:[1]

What you have is not a composite key but many to many relationships. https://en.wikipedia.org/wiki/Many-to-many_(data_model). in this case, the foreign keys in the Junction tables can be treated as a composite primary key, but that is not the case with all Junction tables. For my working example I pulled function out of TblTrabajadores forming another Many to Many relationship but in this case a Trabajadore can hold the same cargo at different times so TrabajadoreID and CargoID are not guaranteed to be a unique pair and hence cannot be a composite primary key.

enter image description here

I've circled the junction tables forming the many to many relationships. For the english speaking, this is a rather standard employee database. employees(Trabajadors), Positions(Cargos), Skills(competencias), employee skill(valor), skill required for the position(valor_esperado). What follows is only a solution using Dlookup and calculated fields. I leave it to someone with better skills in sql theory and subqueries to come up with a full explanation for the query difficulty and a best approach.

I start with a simple query to get the first columns and the right query shape.

enter image description here

enter image description here

Then I add calculated fields for the remaining columns and use Dlookup to get the values based on the row values I have.

enter image description here

enter image description here


FullName: [FirstName] & " " & [LastName]

CargoDescripcion: DLookUp("CargoDescripcion","qryCargoDescripcionsCompetencias","CompetenciaID= " & [TrabajadoresCompetencias].[CompetenciaID])

ValorEsperado: DLookUp("ValorEsperado","CargosCompetencias","competenciaID = " & [Competencies].[CompetenciaID])

'and qryCargoDescripcionsCompetencias which is a little query visually simplifies the DLookup for CargoDescripcion

qryCargoDescripcionsCompetencias: SELECT Cargos.CargoDescripcion, CargosCompetencias.CompetenciaID
FROM Cargos INNER JOIN CargosCompetencias ON Cargos.CargoID = CargosCompetencias.CargoID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mazoula

Related Questions