Way to compare requirements with actual?

[Guest]

Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform includes
their training( from a training table; they are both linked by Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student has taken
with the standard requirements for that employee? ( We have about 4 types of
employee) The class Name field in my training subform is a lookup to a class
table, which just lists the classes offered.

Hopeful of a solution,

John.

 

[PC Datasheet]

You should have a table of classes offered and a table that records the
classes employees have taken. Open the database window to queries and click
on new. One of the choices is Find Unmatched Query Wizard. Use that option
to create a query where you set the employee criteria for the employee you
want and the query returns the classes in the classes offered table that are
not in the table that records the classes the employee has taken. These will
be the classes the employee has not taken.

 

[Rob Oldfield]

It depends on how you have the tables set up that records which classes are
required. The way that I'd do it would be to have three tables....

tblEmployeeType
EmployeeTypeID (pk, AutoNumber)
EmployeeType (text)
....anything else specific to the type

tblClasses
ClassID (pk, AutoNumber)
ClassName (text)
....other Class fields

tblRequirements
EmployeeTypeID (long int)
ClassID (long int)

There would be a record in tblRequirements where, for example, ClassID 1 and
3 were required for Type 1, and Classes 1 and 2 for Type 2.

TypeID, ClassID
1,1
1,3
2,1
2,2

Is that what you do have? (You could get away with less tables if there was
no further info to be stored about Types and Classes other than their
names.)

 

[Guest]

Hey PC Datasheet,
Thanks for your reply.
I did create an unmatched query, but it didn't work right.
I have the query that has all employees training and am comparing it to the
list of classes required. I want all classes that do not appear in the
employees training to show up after the query.
I did get some results, but they were incorrect. Here's the SQL:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Not sure what I'm doing wrong but sure I'm close,

John.

 

[Guest]

Hey Rob,
Thanks for your reply; I'm not making much of the different employee types.
I can just filter out what I don't want.
I have two tables, one training, the other training requirements. I'm trying
to use the unmatched query wizard to find out classes that are in one that
don't appear in the other. Here's my SQL from the unmatched query:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Thanks for your help!

John.

 

[PC Datasheet]

You need the tables:
TblEmployee
EmployeeID
FName
LName
etc

TblHourlyRequiredClass
HourlyRequiredClassID
HourlyRequiredClassName

TblClassTakenByEmployee
ClassTakenByEmployeeID
EmployeeID
HourlyRequiredClassID

Create a standard query that includes TblEmployee and
TblClassTakenByEmployee. Join EmployeeID in both tables. Put the following
expression in the first field of the query:
Employee:[LName] & ", " & [FName]
Pull down HourlyRequiredClassID into the second field. Name the query,
QryEmployeeClasses.

Use the Wizard to create an unmatched query. Find HourlyRequiredClassID in
TblHourlyRequiredClass that is not in
QryEmployeeClasses. Match HourlyRequiredClassID in TblHourlyRequiredClass to
HourlyRequiredClassID in
QryEmployeeClasses. Include the field, Employee, in this query. Sort the
Employee field ascending. This query will give you a list of required
classes for each employee that they have not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hey PC Datasheet,
Thanks for your reply.
I did create an unmatched query, but it didn't work right.
I have the query that has all employees training and am comparing it to the
list of classes required. I want all classes that do not appear in the
employees training to show up after the query.
I did get some results, but they were incorrect. Here's the SQL:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Not sure what I'm doing wrong but sure I'm close,

John.

You should have a table of classes offered and a table that records the
classes employees have taken. Open the database window to queries and click
on new. One of the choices is Find Unmatched Query Wizard. Use that option
to create a query where you set the employee criteria for the employee you
want and the query returns the classes in the classes offered table that are
not in the table that records the classes the employee has taken. These will
be the classes the employee has not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


types
of

 

[Rob Oldfield]

There are two things wrong with that (I think, although I am making some
assumptions)....

First is that tblHOURLYrequiredclasses is only (from the look of it)
returning a list of required classes - not a list of required classes per
employee. That means that when you compare that list to qrytrainingall, the
fact that person A has done the class is hiding the fact that person B
hasn't done it yet. To get around that create a query (which I've called
qryHOURLYrequiredclasses) which contains both the employee table, and
tblHOURLYrequiredclasses. No link between the tables and add the employee
number, first, last and client name. Run that to hopefully get an idea of
what I'm talking about.

(The assumption in the above is, by the way, that you're trying to produce a
list of classes required for _all_ staff, not just one at a time.)

Anyway. Second problem is that your subtract query is going the wrong way.
You want records that show in requirements, but not in classes taken. And
you're doing it the other way round. And it also needs to be extended to
take account of the employee numbers you added above. That also means that
you're going to have to link two fields, which means that the wizard isn't
going to cope.

So...

Start a new query and add both qryHOURLYrequiredclasses and qrytrainingall
onto it. Create links between the two employee number fields, and also
between the two class name fields. You want all of the records that appear
in qryHOURLYrequiredclasses.... so on both of those links right click the
line and pick out the join properties, then choose the option that says
'Select all rows from qryHOURLYrequiredclasses....'. Add all the fields
from qryHOURLYrequiredclasses and the employee number and class name from
qrytrainingall.... and try running that.

Hopefully that makes sense and you can see that the last step is just to add
the criteria Null to both qrytrainingall.[Class Name] and
qrytrainingall.[Employee Number]

My SQL looks like this, if that doesn't work...

SELECT qryrequirements.*
FROM qryrequirements LEFT JOIN qrytrainingall ON (qryrequirements.[Class
Name] = qrytrainingall.[Class Name]) AND (qryrequirements.[Employee number]
= qrytrainingall.[Employee number])
WHERE (((qrytrainingall.[Employee number]) Is Null) AND
((qrytrainingall.[Class Name]) Is Null));

Hey Rob,
Thanks for your reply; I'm not making much of the different employee types.
I can just filter out what I don't want.
I have two tables, one training, the other training requirements. I'm trying
to use the unmatched query wizard to find out classes that are in one that
don't appear in the other. Here's my SQL from the unmatched query:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Thanks for your help!

John.

It depends on how you have the tables set up that records which classes are
required. The way that I'd do it would be to have three tables....

tblEmployeeType
EmployeeTypeID (pk, AutoNumber)
EmployeeType (text)
....anything else specific to the type

tblClasses
ClassID (pk, AutoNumber)
ClassName (text)
....other Class fields

tblRequirements
EmployeeTypeID (long int)
ClassID (long int)

There would be a record in tblRequirements where, for example, ClassID 1 and
3 were required for Type 1, and Classes 1 and 2 for Type 2.

TypeID, ClassID
1,1
1,3
2,1
2,2

Is that what you do have? (You could get away with less tables if there was
no further info to be stored about Types and Classes other than their
names.)


types
of

 

[Guest]

Hey PC Datasheet,
This is weird, but I'm following your instructions to the letter.
In the wizard window where you match the fields, my hourlyrequiredclassID is
not showing up in that window, even when it shows up in the query!
Here's the SQL from the query:

SELECT [Last] & ", " & [First] AS Employee, tbltraining.DSEReqID,
tblstudents.Status
FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] =
tbltraining.[Employee Number]
WHERE (((tblstudents.Status)="active"));

Quite puzzled,

John.

You need the tables:
TblEmployee
EmployeeID
FName
LName
etc

TblHourlyRequiredClass
HourlyRequiredClassID
HourlyRequiredClassName

TblClassTakenByEmployee
ClassTakenByEmployeeID
EmployeeID
HourlyRequiredClassID

Create a standard query that includes TblEmployee and
TblClassTakenByEmployee. Join EmployeeID in both tables. Put the following
expression in the first field of the query:
Employee:[LName] & ", " & [FName]
Pull down HourlyRequiredClassID into the second field. Name the query,
QryEmployeeClasses.

Use the Wizard to create an unmatched query. Find HourlyRequiredClassID in
TblHourlyRequiredClass that is not in
QryEmployeeClasses. Match HourlyRequiredClassID in TblHourlyRequiredClass to
HourlyRequiredClassID in
QryEmployeeClasses. Include the field, Employee, in this query. Sort the
Employee field ascending. This query will give you a list of required
classes for each employee that they have not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hey PC Datasheet,
Thanks for your reply.
I did create an unmatched query, but it didn't work right.
I have the query that has all employees training and am comparing it to the
list of classes required. I want all classes that do not appear in the
employees training to show up after the query.
I did get some results, but they were incorrect. Here's the SQL:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Not sure what I'm doing wrong but sure I'm close,

John.

You should have a table of classes offered and a table that records the
classes employees have taken. Open the database window to queries and click
on new. One of the choices is Find Unmatched Query Wizard. Use that option
to create a query where you set the employee criteria for the employee you
want and the query returns the classes in the classes offered table that are
not in the table that records the classes the employee has taken. These will
be the classes the employee has not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform includes
their training( from a training table; they are both linked by Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student has taken
with the standard requirements for that employee? ( We have about 4 types
of
employee) The class Name field in my training subform is a lookup to a
class
table, which just lists the classes offered.

Hopeful of a solution,

John.

 

[PC Datasheet]

There is no hourlyrequiredclassID in your tables! Is the primary key of
tbltraining DSEReqID? Do you have the three table structure and is there a
DSEReqID field in the junction table?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hey PC Datasheet,
This is weird, but I'm following your instructions to the letter.
In the wizard window where you match the fields, my hourlyrequiredclassID is
not showing up in that window, even when it shows up in the query!
Here's the SQL from the query:

SELECT [Last] & ", " & [First] AS Employee, tbltraining.DSEReqID,
tblstudents.Status
FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] =
tbltraining.[Employee Number]
WHERE (((tblstudents.Status)="active"));

Quite puzzled,

John.

You need the tables:
TblEmployee
EmployeeID
FName
LName
etc

TblHourlyRequiredClass
HourlyRequiredClassID
HourlyRequiredClassName

TblClassTakenByEmployee
ClassTakenByEmployeeID
EmployeeID
HourlyRequiredClassID

Create a standard query that includes TblEmployee and
TblClassTakenByEmployee. Join EmployeeID in both tables. Put the following
expression in the first field of the query:
Employee:[LName] & ", " & [FName]
Pull down HourlyRequiredClassID into the second field. Name the query,
QryEmployeeClasses.

Use the Wizard to create an unmatched query. Find HourlyRequiredClassID in
TblHourlyRequiredClass that is not in
QryEmployeeClasses. Match HourlyRequiredClassID in TblHourlyRequiredClass to
HourlyRequiredClassID in
QryEmployeeClasses. Include the field, Employee, in this query. Sort the
Employee field ascending. This query will give you a list of required
classes for each employee that they have not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hey PC Datasheet,
Thanks for your reply.
I did create an unmatched query, but it didn't work right.
I have the query that has all employees training and am comparing it

to
the

list of classes required. I want all classes that do not appear in the
employees training to show up after the query.
I did get some results, but they were incorrect. Here's the SQL:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Not sure what I'm doing wrong but sure I'm close,

John.


:

You should have a table of classes offered and a table that records the
classes employees have taken. Open the database window to queries

and
click

on new. One of the choices is Find Unmatched Query Wizard. Use that option
to create a query where you set the employee criteria for the

employee
you

want and the query returns the classes in the classes offered table

that
are

not in the table that records the classes the employee has taken.

These
will

be the classes the employee has not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform includes
their training( from a training table; they are both linked by Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student

has
taken

with the standard requirements for that employee? ( We have about

4
types

of
employee) The class Name field in my training subform is a lookup to a
class
table, which just lists the classes offered.

Hopeful of a solution,

John.

 

[Guest]

Hey PC Datasheet,
The primary key for tbltraining is trainingID; the primary key for my
DSERequiredclasses table is DSEReqID.
my table structure is as follows: I have a student table (or employees) with
PK Employee Number. This is tied to the Training table (PK TrainingID) with
Employee Number as a foreign key. My DSERequiredclasses table has PK DSEReqID.
I included DSEReqID as a foreign key in my student table. I did not link
this to the DSERequiredclasses table though.
I will work through the solution you sent again and check my table structure
and will post back shortly.

Getting frustrated something simple is taking so long,

John.

There is no hourlyrequiredclassID in your tables! Is the primary key of
tbltraining DSEReqID? Do you have the three table structure and is there a
DSEReqID field in the junction table?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hey PC Datasheet,
This is weird, but I'm following your instructions to the letter.
In the wizard window where you match the fields, my hourlyrequiredclassID is
not showing up in that window, even when it shows up in the query!
Here's the SQL from the query:

SELECT [Last] & ", " & [First] AS Employee, tbltraining.DSEReqID,
tblstudents.Status
FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] =
tbltraining.[Employee Number]
WHERE (((tblstudents.Status)="active"));

Quite puzzled,

John.

You need the tables:
TblEmployee
EmployeeID
FName
LName
etc

TblHourlyRequiredClass
HourlyRequiredClassID
HourlyRequiredClassName

TblClassTakenByEmployee
ClassTakenByEmployeeID
EmployeeID
HourlyRequiredClassID

Create a standard query that includes TblEmployee and
TblClassTakenByEmployee. Join EmployeeID in both tables. Put the following
expression in the first field of the query:
Employee:[LName] & ", " & [FName]
Pull down HourlyRequiredClassID into the second field. Name the query,
QryEmployeeClasses.

Use the Wizard to create an unmatched query. Find HourlyRequiredClassID in
TblHourlyRequiredClass that is not in
QryEmployeeClasses. Match HourlyRequiredClassID in TblHourlyRequiredClass to
HourlyRequiredClassID in
QryEmployeeClasses. Include the field, Employee, in this query. Sort the
Employee field ascending. This query will give you a list of required
classes for each employee that they have not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Hey PC Datasheet,
Thanks for your reply.
I did create an unmatched query, but it didn't work right.
I have the query that has all employees training and am comparing it to
the
list of classes required. I want all classes that do not appear in the
employees training to show up after the query.
I did get some results, but they were incorrect. Here's the SQL:

SELECT qrytrainingall.Last, qrytrainingall.First, qrytrainingall.[Class
Name], qrytrainingall.[Employee Number]
FROM qrytrainingall LEFT JOIN tblHOURLYrequiredclasses ON
qrytrainingall.[Class Name] = tblHOURLYrequiredclasses.[Class Name]
WHERE (((tblHOURLYrequiredclasses.[Class Name]) Is Null));

Not sure what I'm doing wrong but sure I'm close,

John.


:

You should have a table of classes offered and a table that records the
classes employees have taken. Open the database window to queries and
click
on new. One of the choices is Find Unmatched Query Wizard. Use that
option
to create a query where you set the employee criteria for the employee
you
want and the query returns the classes in the classes offered table that
are
not in the table that records the classes the employee has taken. These
will
be the classes the employee has not taken.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


message
Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform
includes
their training( from a training table; they are both linked by
Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student has
taken
with the standard requirements for that employee? ( We have about 4
types
of
employee) The class Name field in my training subform is a lookup to a
class
table, which just lists the classes offered.

Hopeful of a solution,

John.

 

[Guest]

Hey Rob,
Thanks for all your help on this - I followed your tips; it didn't work.
Here's what my SQL looked like:

SELECT qryhourlyrequiredclasses.*, qrytrainingall.[Class Name],
qrytrainingall.[Employee Number]
FROM qryhourlyrequiredclasses LEFT JOIN qrytrainingall ON
(qryhourlyrequiredclasses.[Class Name] = qrytrainingall.[Class Name]) AND
(qryhourlyrequiredclasses.tblstudents.[Employee Number] =
qrytrainingall.[Employee Number])
WHERE (((qrytrainingall.[Class Name]) Is Null) AND
((qrytrainingall.[Employee Number]) Is Null));

I'm going to look at this again...pls let me know if you come up with
something.

Feeling like Frodo near Mt Doom,

John.

 

[Guest]

Hey Rob,

IT WORKED! I wasn't linking the right table...

Yahoo! That query was the ballgame right there...I appreciate all your help
- Thank you!

Feeling like Spiderman getting his powers back...

John.

Hey Rob,
Thanks for all your help on this - I followed your tips; it didn't work.
Here's what my SQL looked like:

SELECT qryhourlyrequiredclasses.*, qrytrainingall.[Class Name],
qrytrainingall.[Employee Number]
FROM qryhourlyrequiredclasses LEFT JOIN qrytrainingall ON
(qryhourlyrequiredclasses.[Class Name] = qrytrainingall.[Class Name]) AND
(qryhourlyrequiredclasses.tblstudents.[Employee Number] =
qrytrainingall.[Employee Number])
WHERE (((qrytrainingall.[Class Name]) Is Null) AND
((qrytrainingall.[Employee Number]) Is Null));

I'm going to look at this again...pls let me know if you come up with
something.

Feeling like Frodo near Mt Doom,

John.

Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform includes
their training( from a training table; they are both linked by Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student has taken
with the standard requirements for that employee? ( We have about 4 types of
employee) The class Name field in my training subform is a lookup to a class
table, which just lists the classes offered.

Hopeful of a solution,

John.

 

[Rob Oldfield]

Let me just check.... if you run qryhourlyrequiredclasses then you get a
list of ALL people (and their ID) with the class names that they need to do.
And if you run qrytrainingall then you get much the same thing, but ONLY
showing classes that people have actually done?

Hey Rob,
Thanks for all your help on this - I followed your tips; it didn't work.
Here's what my SQL looked like:

SELECT qryhourlyrequiredclasses.*, qrytrainingall.[Class Name],
qrytrainingall.[Employee Number]
FROM qryhourlyrequiredclasses LEFT JOIN qrytrainingall ON
(qryhourlyrequiredclasses.[Class Name] = qrytrainingall.[Class Name]) AND
(qryhourlyrequiredclasses.tblstudents.[Employee Number] =
qrytrainingall.[Employee Number])
WHERE (((qrytrainingall.[Class Name]) Is Null) AND
((qrytrainingall.[Employee Number]) Is Null));

I'm going to look at this again...pls let me know if you come up with
something.

Feeling like Frodo near Mt Doom,

John.

Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform includes
their training( from a training table; they are both linked by Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student has taken
with the standard requirements for that employee? ( We have about 4 types of
employee) The class Name field in my training subform is a lookup to a class
table, which just lists the classes offered.

Hopeful of a solution,

John.

 

[Rob Oldfield]

That's excellent. Could I borrow Kirsten Dunst for a night out?

Hey Rob,

IT WORKED! I wasn't linking the right table...

Yahoo! That query was the ballgame right there...I appreciate all your help
- Thank you!

Feeling like Spiderman getting his powers back...

John.

Hey Rob,
Thanks for all your help on this - I followed your tips; it didn't work.
Here's what my SQL looked like:

SELECT qryhourlyrequiredclasses.*, qrytrainingall.[Class Name],
qrytrainingall.[Employee Number]
FROM qryhourlyrequiredclasses LEFT JOIN qrytrainingall ON
(qryhourlyrequiredclasses.[Class Name] = qrytrainingall.[Class Name]) AND
(qryhourlyrequiredclasses.tblstudents.[Employee Number] =
qrytrainingall.[Employee Number])
WHERE (((qrytrainingall.[Class Name]) Is Null) AND
((qrytrainingall.[Employee Number]) Is Null));

I'm going to look at this again...pls let me know if you come up with
something.

Feeling like Frodo near Mt Doom,

John.

Hello,

I have a database that keeps track of employee training.
The main form contains the employees bio information, the subform includes
their training( from a training table; they are both linked by Employee
number.)
Employees have to take particular classes to ensure compliance with
regulations. Is there a way to compare the classes each student has taken
with the standard requirements for that employee? ( We have about 4 types of
employee) The class Name field in my training subform is a lookup to a class
table, which just lists the classes offered.

Hopeful of a solution,

John.