Select Records NOT

[Jim]

I am using Access97 in a NT network environment

Created a personnel system that monitors training records
I have a table that contains all the personnel details and
a related table that contains which personnel did what
course.
How can I use the tables above to select just the
personnel that have NOT gone on a particular course ?

Can anyone help ?

Cheers

Jim

 

[Les]

Hi,
This might work - haven't tried it.
Create a query from your courses table of everyone who
took the particular course. Then, create an unmatched
query of personnel table against courses query. It should
give you personnel who didn't take course.

 

[Gary Walter]

I am using Access97 in a NT network environment

Created a personnel system that monitors training records
I have a table that contains all the personnel details and
a related table that contains which personnel did what
course.
How can I use the tables above to select just the
personnel that have NOT gone on a particular course ?

Hi Jim,

Without your table structures this may be
difficult to explain, but....

Michel once demonstrated an elegant SQL for
this problem where you you take the MIN of
a condition of "student took course" and
return only the records where the min of
that condition is 0 (only the students who
have never taken the course are returned).

SELECT StudentID
FROM CourseTaken
GROUP BY StudentID
HAVING MIN(CourseID=n) = 0;

if a student took the course, the condition
will be True = -1...so that record will not
be returned.

If you would like some help constructing
this query, please provide your table structures
and *some sample data.*

Good luck,

Gary Walter

 

[Jim]

I would be grateful of some help,
3 Tables ..
tblPersonnel
PayrollID - number(primary key)
Name - text

tblTrainingCourse
CourseCode - Autonumber(primary key)
CourseTitle - text

tblTrainingEmployee
ID - Autonumber(primary key)
PayrollID - Number
CourseCode - Number

So basically how can I list all the payrollID's in
tblPersonnel that have not had a course I choose from
tblTrainingCourse!CourseCode by looking at
tblTrainingEmployee

Hope I have explained it OK

Cheers, Jim

 

[Jim]

Thanks for your help everyone but I have now solved it

a) Created a query that lists everyone who has been on a
particular course.

b) Then created an unmatched query based on the personnel
table and the query above giving me a list of personnel
who haven't been on that course

It's good to talk ..

cheers

Jim

 

[Gary Walter]

I would be grateful of some help,
3 Tables ..
tblPersonnel
PayrollID - number(primary key)
Name - text

tblTrainingCourse
CourseCode - Autonumber(primary key)
CourseTitle - text

tblTrainingEmployee
ID - Autonumber(primary key)
PayrollID - Number
CourseCode - Number

So basically how can I list all the payrollID's in
tblPersonnel that have not had a course I choose from
tblTrainingCourse!CourseCode by looking at
tblTrainingEmployee

Hi Jim,

Let's start with a query for a specific CourseCode
(say "6").

Start a new query in Design View and
add tblPersonnel and tblTrainingEmployee.

Right-mouse click on tblPersonnel,
choose "Properties," and change its
Alias to p
Likewise, change alias of tblTrainingEmployee
to t

Drag and drop PayrollID from p
onto PayrollID in t to create inner
join between tables (if no join line already
exists between them).

From p, drag and drop fields
PayrollID
Name

{"Name" is reserved word and good idea to change}

down into field row of separate columns of the bottom grid.

Change your query to groupby query by
clicking on "sigma" icon in top menu.

A new row in the grid will be added
that now says "Group By" under each
column.

In a new column in the field row type in

MIN([CourseCode]=6)

then in its groupby row, choose "Having"

and in its Criteria row, type 0

Save your query.

Replace "6" with a valid CourseCode.

Save and run.

Does it show the personnel who have
not taken that course?

Change to SQL View,
copy the sql to clipboard
then paste in a reply to this post
if it does not, or you want further
help.

I would imagine a form with a combo box
bound to tblTrainingCourse. The user would
select a CourseTitle, then click on a command
button that constructs the same sql but with the
CourseCode selected in combo box.

This new sql could be applied to RecordSource
of a new form or report you would then open.

Or...
You could make it a parameter query
simply by changing

MIN([CourseCode]=6)

to

MIN([CourseCode]=[Enter Course Code])

///////////////////////////////////////////
Test with Example Data (changed "Name"):

tblPersonnel
PayrollID EmpName
1 Homer
2 Marge
3 Bart
4 Lisa

tblTrainingCourse
CourseCode CourseTitle
1 A
2 B
3 C
4 D
5 E
6 F

tblTrainingEmployee
ID PayrollID CourseCode
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 2 1
8 2 2
9 2 3
10 2 4
11 2 5
12 3 1
13 4 2

Homer(1) took all courses
Marge(2) took all but course 6
Bart(3) took only course 1
Lisa(4) took only course 2



Good luck,

Gary Walter

 

[Gary Walter]

I would be grateful of some help,
3 Tables ..
tblPersonnel
PayrollID - number(primary key)
Name - text

tblTrainingCourse
CourseCode - Autonumber(primary key)
CourseTitle - text

tblTrainingEmployee
ID - Autonumber(primary key)
PayrollID - Number
CourseCode - Number

So basically how can I list all the payrollID's in
tblPersonnel that have not had a course I choose from
tblTrainingCourse!CourseCode by looking at
tblTrainingEmployee

Hi Jim,

Let's start with a query for a specific CourseCode
(say "6").

Start a new query in Design View and
add tblPersonnel and tblTrainingEmployee.

Right-mouse click on tblPersonnel,
choose "Properties," and change its
Alias to p
Likewise, change alias of tblTrainingEmployee
to t

Drag and drop PayrollID from p
onto PayrollID in t to create inner
join between tables (if no join line already
exists between them).

From p, drag and drop fields
PayrollID
Name

{"Name" is reserved word and good idea to change}

down into field row of separate columns of the bottom grid.

Change your query to groupby query by
clicking on "sigma" icon in top menu.

A new row in the grid will be added
that now says "Group By" under each
column.

In a new column in the field row type in

MIN([CourseCode]=6)

then in its groupby row, choose "Expression"

and in its Criteria row, type 0

Save your query.

Replace "6" with a valid CourseCode.

Save and run.

Does it show the personnel who have
not taken that course?

Change to SQL View,
copy the sql to clipboard
then paste in a reply to this post
if it does not, or you want further
help.

I would imagine a form with a combo box
bound to tblTrainingCourse. The user would
select a CourseTitle, then click on a command
button that constructs the same sql but with the
CourseCode selected in combo box.

This new sql could be applied to RecordSource
of a new form or report you would then open.

Or...
You could make it a parameter query
simply by changing

MIN([CourseCode]=6)

to

MIN([CourseCode]=[Enter Course Code])

///////////////////////////////////////////
Test with Example Data (changed "Name"):

tblPersonnel
PayrollID EmpName
1 Homer
2 Marge
3 Bart
4 Lisa

tblTrainingCourse
CourseCode CourseTitle
1 A
2 B
3 C
4 D
5 E
6 F

tblTrainingEmployee
ID PayrollID CourseCode
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 2 1
8 2 2
9 2 3
10 2 4
11 2 5
12 3 1
13 4 2

Homer(1) took all courses
Marge(2) took all but course 6
Bart(3) took only course 1
Lisa(4) took only course 2

So if run following query
(typing in 6 for parameter)

SELECT
tblPersonnel.PayrollID,
tblPersonnel.EmpName
FROM tblPersonnel INNER JOIN tblTrainingEmployee
ON tblPersonnel.PayrollID = tblTrainingEmployee.PayrollID
GROUP BY tblPersonnel.PayrollID, tblPersonnel.EmpName
HAVING
(((Min([CourseCode]=[Enter Course Code]))=0));

get:

PayrollID EmpName
2 Marge
3 Bart
4 Lisa


Good luck,

Gary Walter

 

[Gary Walter]

I'm sorry but after I typed all of
the above out, you have to use my way.

 

[Guest]

I'm Sorry Gary ... Thanks for all your help though