Compare tables ?

[Guest]

I have been trying all week to work this out, not been able to find the
answer here, or at least one I can understand. I have 5 tables

Table 1, Aproved by
Aproved by id
First Name
Last Names

Table 2, Competencies
Competencies id
Competencies
Competencies level id

Table 3, Competency level
Competency level id
Position held

Table 4, Compeleted tasks
Compeleted task id
Competencies id
Employee id
Date
Aproved by id

Table 5, Employee name
Employee id
First Name
Last Name
Competency level

With a query I have been trying to use the 2 tables Competed taskes and
Competencies to find out which tasks a employee has not completed. I have
tryed to use the unmatched query but this just seems to give me only taskes
that no one has compeleted, not the tasks for an individual, please keep the
answer simple!

Hope all this is clear, thanks Matt1

Ps While I wait for the book "Microsoft Office Acess 2003, step by step" any
general comments?

 

[Steve Schapel]

Matt,

I would do this in 2 steps.

First, make a query that will return a complete list of all Competencies
for all Employees. The SQL of such a query will look something like this...
SELECT [Employee Name].[Employee ID], Competencies.[Competencies ID]
FROM [Employee Name], Competencies

Then, make another query using this first query, plus the Completed
Tasks table, to give you those uncompleted competencies. The SQL of
such a query will look something like this...
SELECT [YourFirstQuery].[Employee ID], [YourFirstQuery].[Competencies ID]
FROM [YourFirstQuery] LEFT JOIN [Completed Tasks]
ON [YourFirstQuery].[Employee ID]=[Completed Tasks].[Employee ID]
AND [YourFirstQuery].[Competencies ID]=[Completed
Tasks].[Competencies ID]
WHERE [Completed Tasks].[Compeleted task id] Is Null

Well, in practice, you would probable also want to include the [Employee
Name] table and the Competencies table into this second query, so that
you can return the actual name of the enployee, and the name of the
competency, but I tried to keep it simple for starter.