Listing fields

[geraldk]

Here is my question:

Want I would like to do for this query is to show all GroupName with TrainMatrixName in it, then if this person (EMPID) has values from tblMatrix then I would like to see LegendLetter assigned to it.

For Example:

GroupName TrainMatrixName LegendLetter

General Orientation X
General ISO 14001 Training IP
Office PowerPoint Training
Office Word Training

So the PowerPoint and Word this employee has no training down yet, but it's listed in tblTrainMatrix table. I know how to show if the employee has completed the training but do not know how to list like the example.



Thank you in advance.

[June7]

Have you tried to build query? What happens? What difficulty are you having? Do you want to provide project for analysis? Make copy, remove confidential data.

[geraldk]

Try to build the query and I list all training that would be available (coming from table tblTrainMatrix) when I join in from tblMatrix it will only show what that employee completed /started. I would like to list all possible training course that our company offers and show which course that employee has taken.

[orange]

You may get some ideas form this example of Left Join

http://www.w3schools.com/sql/sql_join_left.asp

[geraldk]

That's what I am looking for. I will try that. thank you!

[geraldk]

Okay, I have tried that and still not getting the results that I looking for. Here is what I have:

SELECT qryTrainMatrixName.EmpID, qryTrainMatrixName.TrainMatrixName, qryTrainMatrixName.GroupName, qryTrainMatrixName.LegendLetter
FROM qryTrainMatrixName
LEFT JOIN tblTrainMatrix
ON qryTrainMatrixName.TrainMatrixID = tblTrainMatrix.TrainMatrixID
ORDER BY qryTrainMatrixName.EmpID;

Output:

The is what I need to add:



There are four items missing from the TableMatrixName for the first output.

[orange]

Please show the SQL for qryTrainMatrixName.

[geraldk]

qryTrainMatrix Name - SQL as the following:

SELECT tblMatrix.EmpID, tblTrainMatrix.TrainMatrixName, tblGroup.GroupName, tblLegend.LegendLetter, tblMatrix.TrainMatrixID
FROM (tblGroup INNER JOIN tblTrainMatrix ON tblGroup.GroupID = tblTrainMatrix.GroupID)
INNER JOIN (tblLegend INNER JOIN tblMatrix ON tblLegend.LegendID = tblMatrix.LegendID)
ON tblTrainMatrix.TrainMatrixID = tblMatrix.TrainMatrixID;

[orange]

OK, please tell us more about this

There are four items missing from the TableMatrixName for the first output.

[June7]

Want to provide db for analysis? We could resolve this a lot easier and faster if we could analyze the full dataset. Follow instructions at bottom of my post.

[geraldk]

I have the database here for you to review. If you like to see how it flows, open frmEmployee, search for employee 'Me Here' and then go Training Matrix, then Venest Training Tab. The listbox159 is where I would like to have this query listed.

Again, thank you for your help in advance.

[June7]

Issue appears to be with the jointypes of the query. Consider:

SELECT tblMatrix.EmpID, tblTrainMatrix.TrainMatrixName, tblGroup.GroupName, tblLegend.LegendLetter, tblMatrix.TrainMatrixID
FROM (tblGroup RIGHT JOIN tblTrainMatrix ON tblGroup.GroupID = tblTrainMatrix.GroupID) LEFT JOIN (tblLegend RIGHT JOIN tblMatrix ON tblLegend.LegendID = tblMatrix.LegendID) ON tblTrainMatrix.TrainMatrixID = tblMatrix.TrainMatrixID;

[geraldk]

June7, I that works. Thank you for your help.

[geraldk]

This query is going in the right direction. But if I only want to list employee that is equal to 1 and the courses that would be available to them to take. As soon as you add another employee to the mix it won't list the courses that would be available to them.

[June7]

I manually input a record into tblMatrix. No problem seeing the new record in the list box. BTW, had to set properties of listbox.

I just noticed you aren't using autonumber field for primary key fields, just number type. This means the ID will not self-generate and you must manually input the next number, unless you have code to handle this.