A select query that compares 2 tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Any insight appreciated.

I have 2 tables, both containing a "task to do" field.

The first table is a listing of all "tasks to do" defining what tasks are to
be done by people who work in a specific job position. (each person has one
job position and has to complete all of their job position's tasks that are
listed as part of this table)

The second table is a listing of all the person's "tasks to do" that have
been done, with some of the tasks done by that person being tasks that are
required of their job position requirements listed in the first table and
other tasks done by that person are just general tasks that are not part of
any specific job position.

I want to compare the second table to the first and pull out of the second
table ONLY those tasks that a person has done that are part of their required
job position EXCLUDING those tasks from the second table that are not part of
that persons job postion.

Is this done with a query using SQL view ...? Could you provide any generic
code as I am new to sql and vba programming.

THANK YOU VERY MUCH

Craig
 
You just need to join the two tables on the job position and task to do
columns. An INNER JOIN returns rows only where there is a match in both
tables, so by joining the tables on both columns only those rows from table 2
where there is a match in table 1 will be returned. The query would be along
these lines:

SELECT [Table 2].*
FROM [Table 2] INNER JOIN [Table 1]
ON [Table 2].[Job Position] = [Table 1].[Job Position]
AND [Table 2].[Task to Do] = [Table 1].[Task to Do];

It can also be done in query design view by creating two join lines between
the tables, dragging from Job Position in Table 2 to Job Position in Table 1
and then from Task to Do in table 2 to Task to Do in Table1.

Incidentally you could do the converse of this and return the rows from
table 2 which don't have matches in table 1. This is done by using a LEFT
OUTER JOIN and testing for one of the columns in Table 1 being NULL:

SELECT [Table 2].*
FROM [Table 2] LEFT JOIN [Table 1]
ON [Table 2].[Job Position] = [Table 1].[Job Position]
AND [Table 2].[Task to Do] = [Table 1].[Task to Do]
WHERE [Table 1].[Job Position] IS NULL;

Ken Sheridan
Stafford, England
 
thanks so much Ken...it really helped me.

Ken Sheridan said:
You just need to join the two tables on the job position and task to do
columns. An INNER JOIN returns rows only where there is a match in both
tables, so by joining the tables on both columns only those rows from table 2
where there is a match in table 1 will be returned. The query would be along
these lines:

SELECT [Table 2].*
FROM [Table 2] INNER JOIN [Table 1]
ON [Table 2].[Job Position] = [Table 1].[Job Position]
AND [Table 2].[Task to Do] = [Table 1].[Task to Do];

It can also be done in query design view by creating two join lines between
the tables, dragging from Job Position in Table 2 to Job Position in Table 1
and then from Task to Do in table 2 to Task to Do in Table1.

Incidentally you could do the converse of this and return the rows from
table 2 which don't have matches in table 1. This is done by using a LEFT
OUTER JOIN and testing for one of the columns in Table 1 being NULL:

SELECT [Table 2].*
FROM [Table 2] LEFT JOIN [Table 1]
ON [Table 2].[Job Position] = [Table 1].[Job Position]
AND [Table 2].[Task to Do] = [Table 1].[Task to Do]
WHERE [Table 1].[Job Position] IS NULL;

Ken Sheridan
Stafford, England

Craig said:
Hi,

Any insight appreciated.

I have 2 tables, both containing a "task to do" field.

The first table is a listing of all "tasks to do" defining what tasks are to
be done by people who work in a specific job position. (each person has one
job position and has to complete all of their job position's tasks that are
listed as part of this table)

The second table is a listing of all the person's "tasks to do" that have
been done, with some of the tasks done by that person being tasks that are
required of their job position requirements listed in the first table and
other tasks done by that person are just general tasks that are not part of
any specific job position.

I want to compare the second table to the first and pull out of the second
table ONLY those tasks that a person has done that are part of their required
job position EXCLUDING those tasks from the second table that are not part of
that persons job postion.

Is this done with a query using SQL view ...? Could you provide any generic
code as I am new to sql and vba programming.

THANK YOU VERY MUCH

Craig
Click to expand...
Click to expand...
 
Back
Top