Query / Join Problem

[Guest]

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Jeff L]

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv
Tests].Date)>=[Enter begining date] And ([Irv Tests].Date)<=[Enter
ending date])) OR ((([Irv Tests].Date) Is Null));

You are getting only those that have taken the test because of your
Where Clause. Those that have not taken the test yet would not have a
record in the [Irv Tests] table.

I believe the easiest thing to do would be to break this up a bit.
Make a query of all operators. Then make one for Operators that have
taken the test. Then do your left join using the two queries,
outputting all records from the first query (all operators).

Some suggestions:
Instead of [Irv Tests].Date>=[Enter begining date] And ([Irv
Tests].Date)<=[Enter ending date]))
use [Irv Tests].Date) Between [Enter begining date] And [Enter ending
date]

Also, it is not a good idea to use Date as a field name. Date is a
function in Access and it is a reserved word. I would suggest changing
that to a different name.

Hope that helps!

 

[Guest]

Thanks Jeff. I did exactly what you said... and when I ran the wuery, it
still just gave me a list of the people who had been given the test. It does
bot pull out all the names on the Employee/operator list if they have not had
the test yt.

SQL:

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.Employee =
qryOperator109.Employee
GROUP BY qryOperators.Employee, qryOperator109.[Test #], qryOperator109.Date
HAVING (((qryOperator109.[Test #])="109") AND ((qryOperator109.Date) Between
[Enter begining date] And [Enter ending date]))
ORDER BY qryOperators.Employee;

Any thoughts? Thanks again....

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv
Tests].Date)>=[Enter begining date] And ([Irv Tests].Date)<=[Enter
ending date])) OR ((([Irv Tests].Date) Is Null));

You are getting only those that have taken the test because of your
Where Clause. Those that have not taken the test yet would not have a
record in the [Irv Tests] table.

I believe the easiest thing to do would be to break this up a bit.
Make a query of all operators. Then make one for Operators that have
taken the test. Then do your left join using the two queries,
outputting all records from the first query (all operators).

Some suggestions:
Instead of [Irv Tests].Date>=[Enter begining date] And ([Irv
Tests].Date)<=[Enter ending date]))
use [Irv Tests].Date) Between [Enter begining date] And [Enter ending
date]

Also, it is not a good idea to use Date as a field name. Date is a
function in Access and it is a reserved word. I would suggest changing
that to a different name.

Hope that helps!

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

Since your query is looking for populated squares and there is no date in
some of the squares in the colume try using (NRT) for not recieved test or
just a dash, this is the simplest way since a query only looks for populated
information.

 

[Guest]

Where do you suggest I put the NRT or the dash? I am not sure I understand
your point.

Thanks,

Doug

Since your query is looking for populated squares and there is no date in
some of the squares in the colume try using (NRT) for not recieved test or
just a dash, this is the simplest way since a query only looks for populated
information.

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

If you have a table with the names and the dates test taken then the dash
would go where ever there is not a date entered. This way the query will
detect all entries otherwise you would only see the three names with dates.

Name 01-02-06
Name 03-05-06
Name -
Name 04-08-06

Where do you suggest I put the NRT or the dash? I am not sure I understand
your point.

Thanks,

Doug

Since your query is looking for populated squares and there is no date in
some of the squares in the colume try using (NRT) for not recieved test or
just a dash, this is the simplest way since a query only looks for populated
information.

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

I don't think this is even possible using the form I have set up for my
managers to enter the results of the tests they give. I have to guess there
is a way to extract the information I am seeking using a query. I am very
close, just can't seem to find the right combination...

If you have a table with the names and the dates test taken then the dash
would go where ever there is not a date entered. This way the query will
detect all entries otherwise you would only see the three names with dates.

Name 01-02-06
Name 03-05-06
Name -
Name 04-08-06

Where do you suggest I put the NRT or the dash? I am not sure I understand
your point.

Thanks,

Doug

Since your query is looking for populated squares and there is no date in
some of the squares in the colume try using (NRT) for not recieved test or
just a dash, this is the simplest way since a query only looks for populated
information.

:

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));

 

[Guest]

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Jeff L]

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.Employee =
qryOperator109.Employee
GROUP BY qryOperators.Employee, qryOperator109.[Test #],
qryOperator109.Date
HAVING (((qryOperator109.[Test #])="109") AND ((qryOperator109.Date)
Between
Enter begining date] And [Enter ending date]))
ORDER BY qryOperators.Employee;

Again, your criteria will only work on records that have taken the
test. If the operator has not taken it, they will not show up in your
query...EVER.

Should be:

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.EmployeeID =

qryOperator109.EmployeeID
Where ((qryOperator109.Date) Between [Enter begining date] And [Enter
ending date]))
Or qryOperator109.Date Is Null
ORDER BY qryOperators.Employee;

I changed the join to use EmployeeID because potentially you could have
two people with the same name. Also, the Group By really wasn't going
to do anything because the date field would make all your records
unique. An operator wouldn't take the test twice on the same day.

Hope that helps!

Thanks Jeff. I did exactly what you said... and when I ran the wuery, it
still just gave me a list of the people who had been given the test. It does
bot pull out all the names on the Employee/operator list if they have not had
the test yt.

SQL:

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.Employee =
qryOperator109.Employee
GROUP BY qryOperators.Employee, qryOperator109.[Test #], qryOperator109.Date
HAVING (((qryOperator109.[Test #])="109") AND ((qryOperator109.Date) Between
[Enter begining date] And [Enter ending date]))
ORDER BY qryOperators.Employee;

Any thoughts? Thanks again....

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv
Tests].Date)>=[Enter begining date] And ([Irv Tests].Date)<=[Enter
ending date])) OR ((([Irv Tests].Date) Is Null));

You are getting only those that have taken the test because of your
Where Clause. Those that have not taken the test yet would not have a
record in the [Irv Tests] table.

I believe the easiest thing to do would be to break this up a bit.
Make a query of all operators. Then make one for Operators that have
taken the test. Then do your left join using the two queries,
outputting all records from the first query (all operators).

Some suggestions:
Instead of [Irv Tests].Date>=[Enter begining date] And ([Irv
Tests].Date)<=[Enter ending date]))
use [Irv Tests].Date) Between [Enter begining date] And [Enter ending
date]

Also, it is not a good idea to use Date as a field name. Date is a
function in Access and it is a reserved word. I would suggest changing
that to a different name.

Hope that helps!

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

Wow, we are getting closer, but are not there yet...

I pasted your SQL in, changing the EmployeeID back tio Employee (we dont
have that many employees and if two had the same name I would use a middle
initial or something) and it did exactly what I needed... Shows all those who
had tests done w/the date it was done and it shows the names of all the
employees who have not had the test with the date field blank.

But, if I plug in two dates, lets say 5-1-2006 through 7-31-2006, and an
employee was tested 8-10-2006 his name does not show up with a blank date
field. Keep in mind though, if he had never been tested his name would
appear with a blank date field.

There has to be a way, we... errrr.. you are so close.

I really appreciate your time.

Doug

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.Employee =
qryOperator109.Employee
GROUP BY qryOperators.Employee, qryOperator109.[Test #],
qryOperator109.Date
HAVING (((qryOperator109.[Test #])="109") AND ((qryOperator109.Date)
Between
Enter begining date] And [Enter ending date]))
ORDER BY qryOperators.Employee;

Again, your criteria will only work on records that have taken the
test. If the operator has not taken it, they will not show up in your
query...EVER.

Should be:

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.EmployeeID =

qryOperator109.EmployeeID
Where ((qryOperator109.Date) Between [Enter begining date] And [Enter
ending date]))
Or qryOperator109.Date Is Null
ORDER BY qryOperators.Employee;

I changed the join to use EmployeeID because potentially you could have
two people with the same name. Also, the Group By really wasn't going
to do anything because the date field would make all your records
unique. An operator wouldn't take the test twice on the same day.

Hope that helps!

Thanks Jeff. I did exactly what you said... and when I ran the wuery, it
still just gave me a list of the people who had been given the test. It does
bot pull out all the names on the Employee/operator list if they have not had
the test yt.

SQL:

SELECT qryOperators.Employee, qryOperator109.Date
FROM qryOperators LEFT JOIN qryOperator109 ON qryOperators.Employee =
qryOperator109.Employee
GROUP BY qryOperators.Employee, qryOperator109.[Test #], qryOperator109.Date
HAVING (((qryOperator109.[Test #])="109") AND ((qryOperator109.Date) Between
[Enter begining date] And [Enter ending date]))
ORDER BY qryOperators.Employee;

Any thoughts? Thanks again....

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv
Tests].Date)>=[Enter begining date] And ([Irv Tests].Date)<=[Enter
ending date])) OR ((([Irv Tests].Date) Is Null));

You are getting only those that have taken the test because of your
Where Clause. Those that have not taken the test yet would not have a
record in the [Irv Tests] table.

I believe the easiest thing to do would be to break this up a bit.
Make a query of all operators. Then make one for Operators that have
taken the test. Then do your left join using the two queries,
outputting all records from the first query (all operators).

Some suggestions:
Instead of [Irv Tests].Date>=[Enter begining date] And ([Irv
Tests].Date)<=[Enter ending date]))
use [Irv Tests].Date) Between [Enter begining date] And [Enter ending
date]

Also, it is not a good idea to use Date as a field name. Date is a
function in Access and it is a reserved word. I would suggest changing
that to a different name.

Hope that helps!


vladi16 wrote:
I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

This uses two queries to do what you said you wanted.

vladi16-1 ---
SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

Karl,


The first one ran no problem, the second one refers to a table, I think,
named "vladi16 - 1". I have no table of that name. What would that be?

Thanks,

Doug

This uses two queries to do what you said you wanted.

vladi16-1 ---
SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));


:

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

That is the name I gave the first query.

Karl,


The first one ran no problem, the second one refers to a table, I think,
named "vladi16 - 1". I have no table of that name. What would that be?

Thanks,

Doug

This uses two queries to do what you said you wanted.

vladi16-1 ---
SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

:

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));


:

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

99.9% ... The only issue is that when I run the second query it asks me to
enter the date range twice. Any thoughts? Other than this issue it is
absolutely wonderful... Way over my head, but absolutely wonderful.

Also, there was a couple extra spaces between items that I cleaned up.
Below is what I have.

vladi16-1:

SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

query2:

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));

That is the name I gave the first query.

Karl,


The first one ran no problem, the second one refers to a table, I think,
named "vladi16 - 1". I have no table of that name. What would that be?

Thanks,

Doug

This uses two queries to do what you said you wanted.

vladi16-1 ---
SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));


:

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

:

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));


:

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

I created a simply report to show me the reults "Employee and Dates" and if
they had only received the test once it printed fine, but if they took it
twice... it doubled the results. Meaning it showed the two dates twice each
as if they took it 4 times.

Thanks,

Doug

That is the name I gave the first query.

Karl,


The first one ran no problem, the second one refers to a table, I think,
named "vladi16 - 1". I have no table of that name. What would that be?

Thanks,

Doug

This uses two queries to do what you said you wanted.

vladi16-1 ---
SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));


:

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

:

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));


:

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug

 

[Guest]

Woo Hooo.. Never mind my previous two posts.. I got it.

Thanks Karl

That is the name I gave the first query.

Karl,


The first one ran no problem, the second one refers to a table, I think,
named "vladi16 - 1". I have no table of that name. What would that be?

Thanks,

Doug

This uses two queries to do what you said you wanted.

vladi16-1 ---
SELECT [Irv Tests].employee, [Irv Tests].Date
FROM [Irv Tests]
WHERE ((([Irv Tests].Date) Between [Enter begining date] And [Enter ending
date] Or ([Irv Tests].Date) Is Null) AND (([Irv Tests].[Test #])="109"));

SELECT Employee.employee, Employee.position, [Irv Tests].Date, [Irv
Tests].[Test #]
FROM (Employee LEFT JOIN [vladi16-1] ON Employee.employee =
[vladi16-1].employee) LEFT JOIN [Irv Tests] ON [vladi16-1].employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([vladi16-1].employee) Is
Null)) OR (((Employee.position)="Operator") AND (([Irv Tests].[Test
#])="109"));


:

I do not need the information from the last 180 days only... I need the
information for the whole calandar year. Or it may be that I need to be able
to punch in the dates for the last quarter to see what we did for that
period. In short, I need to see a list of all my employees with the dates
the test was performed on them and if they had not received the test, I still
need their name to appear with no dates showing. I just want to be able to
use the "BETWEEN [Enter begining date] AND [Enter ending date]" in criteria
and extract the information.

Thanks very much

:

Try this. You said twice a year so I made it pull any that had it more than
180 days ago. You could make it 150 days so as to plan a month in advance.

SELECT Employee.employee, Employee.position, Max([Irv Tests].Date) AS
MaxOfDate, Date()-180 AS Expr1
FROM Employee LEFT JOIN [Irv Tests] ON Employee.employee = [Irv
Tests].employee
WHERE (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109"))
OR (((Employee.position)="Operator") AND (([Irv Tests].[Test #])="109")) OR
(((Employee.position)="Operator") AND (([Irv Tests].employee) Is Null))
GROUP BY Employee.employee, Employee.position, Date()-180
HAVING (((Max([Irv Tests].Date))<=Date()-180 Or (Max([Irv Tests].Date)) Is
Null));


:

I have a table named "Employee" whih lists all our employee's names, their
department (Transportation, Maintenance, Dispatchers, etc.), and their Titles
(Operator, Attendant, Mechanic, etc).

Our management performs tests and observations (T&Os) on our employees and
we enter the reults from those T&Os on a form and that info gets sent to a
table called "Irv Tests". Of all the various T&Os we do, there is one test
(#109) that every Operator from the Transportation Department must have done
on them twice a year. I am trying to show a Query and ultimately a Report
that will list the names of just the Operators and the dates that they
received this particular test. If they have not received the test yet, I
need their name to still show, but obviously there would not be a date next
to their name.

My attempt to do this (See SQL below) produces a list of only the employees
who have received the test... If you have not received the test your name
would not appear.

SELECT Employee.Employee, [Irv Tests].Date
FROM Employee LEFT JOIN [Irv Tests] ON Employee.Employee = [Irv
Tests].Employee
WHERE ((([Irv Tests].[Test #])="109"))
GROUP BY Employee.Employee, Employee.Position, [Irv Tests].Date
HAVING (((Employee.Position)="Operator") AND (([Irv Tests].Date)>=[Enter
begining date] And ([Irv Tests].Date)<=[Enter ending date])) OR ((([Irv
Tests].Date) Is Null));


I have had the problem in the past and just can't seem to figure it out.

Thanks,

Doug