Solved Issues with query joins (1 Viewer)

[nashaz]

Hi all

In the attached db, I am trying to create a query, MandatoryTrainingDeliveredQ, which takes in info from MandatoryEmployeeTrainingQ and EmployeeXCourse_JT, using EmployeeID as the common field. I am however getting duplicate values. The short description of db is as follows:

• EmployeeT - EmployeeID (PK), FirstName, LastName, PositionID (FK), DeptID (FK)
• CourseNameT - CourseID (PK), and lists all the training courses in CourseName
• CourseDeliveryT - DeliverID (PK), and contains CourseID (FK), CourseCode, ProviderID (FK), Cost

This is so that each course may be delivered by multiple providers.

• EmployeeXCourse_JT - EmployeeID, DeliverID, CertificationDate (all three are PK and form composite key, to avoid duplication), CourseID (FK). Lists all the training provided to employees
• RoleXCourse_JT - ID (PK), PositionID (FK), CourseID (FK). Denotes courses mandatory for each role
• RoleXEmployee_JT - ID (PK), PositionID (FK), EmployeeID (FK). Denotes roles assigned to each employee

I then have MandatoryEmployeeTrainingQ which is based on the RoleXCourse and RoleXEmployee tables, and denotes which roles have been assigned to which employee, and which courses they must be trained on.

Now to get the record of JUST the mandatory trainings, I am trying to create a new query called MandatoryTrainingDeliveredQ and trying to attach above query with EmployeeXCourse table. In the output, I should only get the mandatory trainings which have been delivered i.e., have a certificationdate.

Many many thanks for your time and help, in advance

 

[nashaz]

For reference, here are the images:

And the output of the two:

 

[The_Doc_Man]

It would help if you described in common conversational terms what you wanted to accomplish. We see some hints, but give us the non-technical overview of the purpose here.

 

[jdraw]

Haven't looked at your database, but "duplicate" may be mis-stated. Your output of the two shows records with different certification dates.

 

[nashaz]

It would help if you described in common conversational terms what you wanted to accomplish. We see some hints, but give us the non-technical overview of the purpose here.

Hi Doc

Apologies. What I am trying to achieve here is that some of the courses are mandatory according to roles assigned to employees (the same course may be delivered to other employees even though its not mandatory). So I assign mandatory courses to roles in a RoleXCourse table. The table RoleXEmployee assigns the roles to the employees. Bring the two tables together in a query to give me a list of mandatory courses assigned to each employee. I then want to match the records from this query to records in EmployeeXCourse table to see which courses from the first query are delivered. I will show that record in a subform of the main employee form to keep track of things.

 

[nashaz]

Haven't looked at your database, but "duplicate" may be mis-stated. Your output of the two shows records with different certification dates.

Hi jdraw

yes you are right. the records aren't duplicated, rather mismatched, if that makes sense?

 

[nashaz]

One more thing to add: I can add a CourseID FK in the EmployeeXCourse table which gives me the required results but then the issue is when I enter data in the EmployeeXCourse subform, the CourseID field remains at 0, which kind of fails the point. So any solution around that would also be appropriate.

 

[nector]

It looks like relationship was not followed:

Employee --------> Course name ---------> Course delivery

Employee ---One ----->Course name -----Many = child course delivery

 

[arnelgp]

see the change i made on MandatoryTrainingDeliveredQ query.

 

[ebs17]

I'm just watching. The beginning is by looking at the relationship diagram, including trying to understand the connections. Only these relationships are interesting for creating queries; any form histories are placed far behind.

1) Query problems can arise from structural problems. With real relationships you often have simple logic and simple queries.
2) You have relationships that interfere with each other.
- The relationship RoleXCount to RoleXEmployee is incorrect because the PositionID is already linked via PositionT.
- The PositionID field in EmployeeT is incorrect, as is the associated relationship, since the link is made via its own RoleXEmployee table.

I have corrected that in the attachment.

qryMandatoryCourses

SELECT
   RoleXEmployee_JT.EmployeeID,
   RoleXCourse_JT.CourseID
FROM
   (PositionT
      INNER JOIN RoleXEmployee_JT
      ON PositionT.PositionID = RoleXEmployee_JT.PositionID
   )
   INNER JOIN RoleXCourse_JT
   ON PositionT.PositionID = RoleXCourse_JT.PositionID

qryExistingCourses

SELECT
   EmployeeXCourse_JT.EmployeeID,
   CourseNameT.CourseID
FROM
   CourseNameT
      INNER JOIN
         (CourseDeliveryT
            INNER JOIN EmployeeXCourse_JT
            ON CourseDeliveryT.DeliverID = EmployeeXCourse_JT.DeliverID
         )
      ON CourseNameT.CourseID = CourseDeliveryT.CourseID

As you can see, I'll initially limit myself to ID and necessary fields. Names of employees and names of courses can be added later if necessary or linked to additional tables. In the design phase I have the principle: first calculate, then connect.

You can now compare the two queries against each other.

 

[nashaz]

see the change i made on MandatoryTrainingDeliveredQ query.

Hi arnel

Appreciate it. It is working the way I originally intended. I have run into the issue as I explained in the later comment now I apologise for this.

 

[nashaz]

I'm just watching. The beginning is by looking at the relationship diagram, including trying to understand the connections. Only these relationships are interesting for creating queries; any form histories are placed far behind.

1) Query problems can arise from structural problems. With real relationships you often have simple logic and simple queries.
2) You have relationships that interfere with each other.
- The relationship RoleXCount to RoleXEmployee is incorrect because the PositionID is already linked via PositionT.
- The PositionID field in EmployeeT is incorrect, as is the associated relationship, since the link is made via its own RoleXEmployee table.

I have corrected that in the attachment.

qryMandatoryCourses

SELECT
   RoleXEmployee_JT.EmployeeID,
   RoleXCourse_JT.CourseID
FROM
   (PositionT
      INNER JOIN RoleXEmployee_JT
      ON PositionT.PositionID = RoleXEmployee_JT.PositionID
   )
   INNER JOIN RoleXCourse_JT
   ON PositionT.PositionID = RoleXCourse_JT.PositionID

qryExistingCourses

SELECT
   EmployeeXCourse_JT.EmployeeID,
   CourseNameT.CourseID
FROM
   CourseNameT
      INNER JOIN
         (CourseDeliveryT
            INNER JOIN EmployeeXCourse_JT
            ON CourseDeliveryT.DeliverID = EmployeeXCourse_JT.DeliverID
         )
      ON CourseNameT.CourseID = CourseDeliveryT.CourseID

As you can see, I'll initially limit myself to ID and necessary fields. Names of employees and names of courses can be added later if necessary or linked to additional tables. In the design phase I have the principle: first calculate, then connect.

You can now compare the two queries against each other.

Hi ebs

Re point 2), the reason I had done that was because the EmployeeT will have the main role and additional roles would go into the subform but you are right, it doesn't have to be that way. Your approach has actually cleared up a few confusions for me, and I am hopeful I can get it working the way I wanted it to now. Thank you very much!