Joining two queries

[Shanin]

I have two queries that I would like to combine the results. Actually I have
one query I'd like to join with multiple others. I have a database that
tracks trainings, so there is a tblEmployees, tblTrainings, and
tblTrainingList (this one just has a list of all trainings that need to be
taken). I have one query that compares what an employee has taken and what
they haven't and shows what they still need to take, here is the code:

SELECT [LastName] & "," & [FirstName] AS Name, T2.Training, T1.DateHired
FROM tblEmployees AS T1, tblTrainingList AS T2
WHERE (((T2.Training) Not In (SELECT DISTINCT Training FROM tblTraining
WHERE EmployeeID = T1.EmployeeID)) AND ((T1.Separated)=No)) AND
(T1.TrainingsRequired)=Yes
ORDER BY [LastName] & "," & [FirstName]
WITH OWNERACCESS OPTION;

I have several other queries that allow you to view trainings that will
expire within a date range, or training by employee, by supervisor, etc. The
only bad thing about these is it will onlyl show those trainings they
currently have date in for and will not show those they have yet to take.
Here is an example of the one by employee:

SELECT [FirstName] & " " & [LastName] AS Name, tblTraining.Training,
tblTraining.DateTaken, tblTraining.DateExpires,
IIf([DateExpires]<Date(),"yes") AS Late, tblTraining.SignedUp,
tblTraining.PreviousDueDate, tblTraining.[DateTurnedIn/Taken]
FROM tblEmployees INNER JOIN tblTraining ON tblEmployees.EmployeeID =
tblTraining.EmployeeID
WHERE (((tblEmployees.EmployeeID)=[Forms]![TrainingbyEmployee]![Combo0]))
WITH OWNERACCESS OPTION;

Is there anyway I can get the results from my first query to also be in
these others, so if someone looks by employee, it will show all, even those
they haven't taken, or if they search by date range, it will still list those
that employees have not taken?

Thanks

 

[[MVP] S.Clark]

The standard answer is:

Break the query or queries into multiple(or more) queries. Write data to
temp tables as needed, then use other action queries to massage the data
into the shape that you need.

As you have Queries with Sub-Queries and an IIF() to boot, it tells me that
you have approached this from the wrong angle to start. I have no other
clues as to where this train derailed.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Shanin]

Well I created the one with advice from here since I was having a hard time
at getting something to show me who had not had a particular training. It
works great for that.

Should I have made the tables different? I have the tblEmployee, which
basically has all the employee info, address, etc. The tblTraining, is what
keeps track of the date they take the training, expiration, etc, and the
tblTraininList is just a table of names of trainings for a combo box to
choose when entering data.

I'm not sure how I can break down the queries anymore since they are only
doing one thing, the one checks for missing, and the others just sort by
different criteria.

The standard answer is:

Break the query or queries into multiple(or more) queries. Write data to
temp tables as needed, then use other action queries to massage the data
into the shape that you need.

As you have Queries with Sub-Queries and an IIF() to boot, it tells me that
you have approached this from the wrong angle to start. I have no other
clues as to where this train derailed.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I have two queries that I would like to combine the results. Actually I
have
one query I'd like to join with multiple others. I have a database that
tracks trainings, so there is a tblEmployees, tblTrainings, and
tblTrainingList (this one just has a list of all trainings that need to be
taken). I have one query that compares what an employee has taken and
what
they haven't and shows what they still need to take, here is the code:

SELECT [LastName] & "," & [FirstName] AS Name, T2.Training, T1.DateHired
FROM tblEmployees AS T1, tblTrainingList AS T2
WHERE (((T2.Training) Not In (SELECT DISTINCT Training FROM tblTraining
WHERE EmployeeID = T1.EmployeeID)) AND ((T1.Separated)=No)) AND
(T1.TrainingsRequired)=Yes
ORDER BY [LastName] & "," & [FirstName]
WITH OWNERACCESS OPTION;

I have several other queries that allow you to view trainings that will
expire within a date range, or training by employee, by supervisor, etc.
The
only bad thing about these is it will onlyl show those trainings they
currently have date in for and will not show those they have yet to take.
Here is an example of the one by employee:

SELECT [FirstName] & " " & [LastName] AS Name, tblTraining.Training,
tblTraining.DateTaken, tblTraining.DateExpires,
IIf([DateExpires]<Date(),"yes") AS Late, tblTraining.SignedUp,
tblTraining.PreviousDueDate, tblTraining.[DateTurnedIn/Taken]
FROM tblEmployees INNER JOIN tblTraining ON tblEmployees.EmployeeID =
tblTraining.EmployeeID
WHERE (((tblEmployees.EmployeeID)=[Forms]![TrainingbyEmployee]![Combo0]))
WITH OWNERACCESS OPTION;

Is there anyway I can get the results from my first query to also be in
these others, so if someone looks by employee, it will show all, even
those
they haven't taken, or if they search by date range, it will still list
those
that employees have not taken?

Thanks