Query to show entries from 2 tables not in third table

[Isskint]

I have been crunching this all day, but i am missing something, so hopefully one of you guys will solve it.

I have 3 tables;
tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process

tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID

When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.
In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!:banghead:

So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.

I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).

Thank you all in advance for your efforts.

 

[rzw0wr]

Question, How would Access know if something is missing?
You can compare 2 tables for missing data in 1 table.
You need something to compare to.

Do you see what I am saying.
If the data is not there Access does not know this.

Example:
If you have 4 steps to a process and 3 people have not gone through number 3. How would Access know that these people have not completed the 4th process?

I think it is possibly your tables need a little work.
Check them over and Google normalization.

Also I never use inner or out joins in a relationship.
Do that in a query.

Dale

 

[Isskint]

Thanks for the input Dale.

Just to answer your question and hopefully provide you with some further insight.

Question, How would Access know if something is missing?
You can compare 2 tables for missing data in 1 table.
You need something to compare to.

As i stated, the third table stores data on which employees have trained on which process. There is 1 record for every employee for EVERY process (regardless of training completion). Have a look at tblTraining. EDIT: Dale, you just keep on saying what you think, It got the little cogs working again and resolved my issue, THANKYOU THANKYOU THANKYOU THANKYOU

I think it is possibly your tables need a little work.
Check them over and Google normalization.

Normalization My tables are Normalised (2NF i believe?)

Also I never use inner or out joins in a relationship.
Do that in a query.

When you set up table relationships, these ARE joins.

 

[Isskint]

OK problem solved.

I was too far into the problem and got lost on what i wanted to do (could not see the wood for the trees:banghead::banghead::banghead

Dale (rzw0wr) put me onto it, so big thanks there.

I am looking for something that should be there BUT that is not there!!

So 1 query to give me a 'perfect list' - every employee for every process.

SELECT [tblTraining]![empID] & "-" & [tblTraining]![pcsID] AS fxId
FROM tblTraining;

Then another query to COUNT the occurrences of the fxID, looking for count = 0.

SELECT tblEmployees.empID, tblProcess.pcsID, DCount("fxID","qryFullEmpPrcs","fxID='" & ([empID] & "-" & [pcsID]) & "'") AS CountfxID, tblEmployees.empLastName, tblEmployees.empFirstName, tblProcess.pcsTitle, tblProcess.pcsDescription
FROM tblEmployees, tblProcess
WHERE (((DCount("fxID","qryFullEmpPrcs","fxID='" & ([empID] & "-" & [pcsID]) & "'"))=0));

If there is a better way, I am, as always, longing to learn.

 

[rzw0wr]

Quote:
Originally Posted by rzw0wr
Also I never use inner or out joins in a relationship.
Do that in a query.

When you set up table relationships, these ARE joins.

Never said they weren't.
I said I never use these types in a relationship. I use then in queries only.

Dale

 

[Isskint]

Never said they weren't.
I said I never use these types in a relationship. I use then in queries only.

You are quite right, you did not say that. However just think about table level relationships. Creating relationships between tables is the very heart of a relational database. It is what links the data of tables together and enables the cascading abilities that make changes and deletions across multiple tables, so much easier.

But once again, I thank you for your thoughts, without which i would not have resolved my issue.