Query that seems impossible

[Gaetanm via AccessMonster.com]

I have received much help from this Group and I hope someone can
help me with this:

My form has two combo box’s [cboEmployeeId] [cboJobId]the purpose of the form
is
to pull the emp id and job Id click a button the Now() function executes into
a text field and Click another button to record into my Clock_table.

I have another form that is about the same but enters a stopdate

In my Clock_table I would show [EmployeeId][JobId] [Startime] StopTime]
And I do my time calculation queries from here.

All this works but with this wrinkle.

An employee may punch in and out 20 times during the day going job to job
Including some he has worked on during the day.

I need to pull up the employeeID that is starting a job that be one he has
worked on before or not making Sure he doe’s not have another job open.
In other words he can’t clock in until he has clocked out.

Below is the code for checking on STOP time. This sees if the
Stopdate is null if so the combo box on the STOP time form pulls
Up only the EmployeeID that have jobs open.

I would like to bring up in the START time form Employees that
Do not have jobs open

Here is my code for jobs Not completed

SELECT Employees.EmployeeID, Employees.FirstName, Employees.MiddleName,
Employees.LastName, [Job Sheet].JobID, [Job Sheet].Notes, Clock_Table.
StartDate, Clock_Table.StopDate
FROM Employees INNER JOIN ([Job Sheet] INNER JOIN Clock_Table ON [Job Sheet].
JobID = Clock_Table.JobID) ON Employees.EmployeeID = Clock_Table.EmployeeID
WHERE (((Clock_Table.StartDate) Is Not Null) AND ((Clock_Table.StopDate) Is
Null));

Thanks

Gaetanm

 

[Allen Browne]

So you want to select all the employees who are clocked in, where "clocked
in" means there last record for this employee has Null in the StopDate
field.

You could do that with a subquery:

SELECT Employees.* FROM Employees
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.ID DESC) Is Null;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Gaetanm via AccessMonster.com]

So you want to select all the employees who are clocked in, where "clocked
in" means there last record for this employee has Null in the StopDate
field.

You could do that with a subquery:

SELECT Employees.* FROM Employees
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.ID DESC) Is Null;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I have received much help from this Group and I hope someone can
help me with this:

[quoted text clipped - 40 lines]

Is
Null));

Allen thanks for the quick reply

What I need is all employees that are Not clocked in. They have a
StartDate and a Stopdate completed. Both of these are NOT Null.

The problem is that some people that ARE clocked in will
also have StartDate and StopDate Not Null and have a present Stopdate
as Null.

Any Ideas

Gaetan

 

[Allen Browne]

Insert NOT as the 2nd last word:

SELECT Employees.* FROM Employees
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.ID DESC) Is Not Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

So you want to select all the employees who are clocked in, where "clocked
in" means there last record for this employee has Null in the StopDate
field.

You could do that with a subquery:

SELECT Employees.* FROM Employees
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.ID DESC) Is Null;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I have received much help from this Group and I hope someone can
help me with this:

[quoted text clipped - 40 lines]

Is
Null));

Allen thanks for the quick reply

What I need is all employees that are Not clocked in. They have a
StartDate and a Stopdate completed. Both of these are NOT Null.

The problem is that some people that ARE clocked in will
also have StartDate and StopDate Not Null and have a present Stopdate
as Null.

 

[Gaetanm via AccessMonster.com]

Insert NOT as the 2nd last word:

SELECT Employees.* FROM Employees
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.ID DESC) Is Not Null;
[quoted text clipped - 24 lines]

also have StartDate and StopDate Not Null and have a present Stopdate
as Null.

Allen

I took a look at that MS article bout sub queries. I'm afraid I'm new at this
what I don't understand in your example is a subquery to what?
Can you help

Thanks

Gaetanm

 

[Allen Browne]

The main query in the example starts with SELECT.

The subquery is in brackets.
It is another complere SELECT statement inside the main query.

The subquery selects the most recent StopDate for the employee in the main
query (i.e. the TOP 1, when sorted by the StartDate in descending order.)

The main query then examines whether the value returned from the subquery Is
Not Null. If that's the case, the main query chooses the employee.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Insert NOT as the 2nd last word:

SELECT Employees.* FROM Employees
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.ID DESC) Is Not Null;

So you want to select all the employees who are clocked in, where
"clocked
in" means there last record for this employee has Null in the StopDate

[quoted text clipped - 24 lines]

also have StartDate and StopDate Not Null and have a present Stopdate
as Null.

Allen

I took a look at that MS article bout sub queries. I'm afraid I'm new at
this
what I don't understand in your example is a subquery to what?
Can you help

 

[Gaetanm via AccessMonster.com]

The main query in the example starts with SELECT.

The subquery is in brackets.
It is another complere SELECT statement inside the main query.

The subquery selects the most recent StopDate for the employee in the main
query (i.e. the TOP 1, when sorted by the StartDate in descending order.)

The main query then examines whether the value returned from the subquery Is
Not Null. If that's the case, the main query chooses the employee.
[quoted text clipped - 16 lines]

what I don't understand in your example is a subquery to what?
Can you help

Allen
Hope you had a good Thanksgiving

I'm still not getting the results I need I'm proberly doing something wrong.

When I run the query it ask me for an employee number then
the results I get is all the Is Not Null that is in my table. If I
put an employee (ie #1) that stopdate is not null it comes up blank
thats a good thing. But if I choose an employee that Is Not Null
All the Employees show including employee #1 that Is Not Null which
is a bad thing.

Also the purpose of this query is to have a combo box on a form give
me the choices pick from of employees that do not have a job open.

Below is my query

SELECT Employees.EmployeeID, Employees.FirstName, Employees.MiddleName,
Employees.LastName, [Job Sheet].JobID, [Job Sheet].Notes, Clock_Table.
StartDate, Clock_Table.StopDate
FROM Employees INNER JOIN ([Job Sheet] INNER JOIN Clock_Table ON [Job Sheet].
JobID = Clock_Table.JobID) ON Employees.EmployeeID = Clock_Table.EmployeeID
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.StopDate DESC) Is Not Null;

Gaetanm

 

[Allen Browne]

The subquery is using Clock_Table, and so is the main table.
You need to alias one copy so Access can tell the difference.

The subquery sorts by StartDate descending so that the TOP 1 is the most
recent record for the employee. If there were 2 records for the same
employee starting at the same time (that shouldn't happen, but if it did),
the subquery would have 2 identical records and Access would return 2
instead of 1 and the query would fail. To avoid that, we add the primary key
value of the table to the ORDER BY clause of the subquery.

The whole query now looks like this:
SELECT Employees.EmployeeID,
Employees.FirstName,
Employees.MiddleName,
Employees.LastName,
[Job Sheet].JobID,
[Job Sheet].Notes,
Clock_Table.StartDate,
Clock_Table.StopDate
FROM Employees INNER JOIN ([Job Sheet] INNER JOIN Clock_Table
ON [Job Sheet].JobID = Clock_Table.JobID)
ON Employees.EmployeeID = Clock_Table.EmployeeID
WHERE (SELECT TOP 1 Dupe.StopDate
FROM Clock_Table AS Dupe
WHERE Dupe.EmployeeID = Employees.EmployeesID
ORDER BY Dupe.StartDate DESC, Dupe.ID DESC) Is Not Null;

If the query asks for a parameter, it means there is a name it can't figure
out. This might be a misspelling, or a field associated with the wrong
table, or ...

Once you have the query working, you can use it as the RowSource for your
combo. The combo probably doesn't need all 8 rows here, so you can uncheck
the "Show" box under some of those columns.

There is an issue with the inner joins. If an employee has never clocked in,
they won't show in this query, and if they have clocked in multiple times
the main query will show them multiple times. I'm not clear why Clock_Table
and [Job Sheet] are needed in this query. You may be able to drop those
tables out, and just let the subquery do the work. Personally I would be
trying to get this down to just 2 columns, like this:

SELECT Employees.EmployeeID,
Trim(Employees.LastName & ", " & Employees.FirstName & " "
& Employees.MiddleName) AS EmployeeName
FROM Employees
WHERE (SELECT TOP 1 Dupe.StopDate
FROM Clock_Table AS Dupe
WHERE Dupe.EmployeeID = Employees.EmployeesID
ORDER BY Dupe.StartDate DESC, Dupe.ID DESC) Is Not Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The main query in the example starts with SELECT.

The subquery is in brackets.
It is another complere SELECT statement inside the main query.

The subquery selects the most recent StopDate for the employee in the main
query (i.e. the TOP 1, when sorted by the StartDate in descending order.)

The main query then examines whether the value returned from the subquery
Is
Not Null. If that's the case, the main query chooses the employee.

Insert NOT as the 2nd last word:

[quoted text clipped - 16 lines]

what I don't understand in your example is a subquery to what?
Can you help

Allen
Hope you had a good Thanksgiving

I'm still not getting the results I need I'm proberly doing something
wrong.

When I run the query it ask me for an employee number then
the results I get is all the Is Not Null that is in my table. If I
put an employee (ie #1) that stopdate is not null it comes up blank
thats a good thing. But if I choose an employee that Is Not Null
All the Employees show including employee #1 that Is Not Null which
is a bad thing.

Also the purpose of this query is to have a combo box on a form give
me the choices pick from of employees that do not have a job open.

Below is my query

SELECT Employees.EmployeeID, Employees.FirstName, Employees.MiddleName,
Employees.LastName, [Job Sheet].JobID, [Job Sheet].Notes, Clock_Table.
StartDate, Clock_Table.StopDate
FROM Employees INNER JOIN ([Job Sheet] INNER JOIN Clock_Table ON [Job
Sheet].
JobID = Clock_Table.JobID) ON Employees.EmployeeID =
Clock_Table.EmployeeID
WHERE (SELECT TOP 1 Clock_Table.StopDate FROM Clock_Table
WHERE Clock_Table.EmployeeID = Employees.EmployeesID
ORDER BY Clock_Table.StartDate DESC, Clock_Table.StopDate DESC) Is Not
Null;

 

[Gaetanm via AccessMonster.com]

The subquery is using Clock_Table, and so is the main table.
You need to alias one copy so Access can tell the difference.

The subquery sorts by StartDate descending so that the TOP 1 is the most
recent record for the employee. If there were 2 records for the same
employee starting at the same time (that shouldn't happen, but if it did),
the subquery would have 2 identical records and Access would return 2
instead of 1 and the query would fail. To avoid that, we add the primary key
value of the table to the ORDER BY clause of the subquery.

The whole query now looks like this:
SELECT Employees.EmployeeID,
Employees.FirstName,
Employees.MiddleName,
Employees.LastName,
[Job Sheet].JobID,
[Job Sheet].Notes,
Clock_Table.StartDate,
Clock_Table.StopDate
FROM Employees INNER JOIN ([Job Sheet] INNER JOIN Clock_Table
ON [Job Sheet].JobID = Clock_Table.JobID)
ON Employees.EmployeeID = Clock_Table.EmployeeID
WHERE (SELECT TOP 1 Dupe.StopDate
FROM Clock_Table AS Dupe
WHERE Dupe.EmployeeID = Employees.EmployeesID
ORDER BY Dupe.StartDate DESC, Dupe.ID DESC) Is Not Null;

If the query asks for a parameter, it means there is a name it can't figure
out. This might be a misspelling, or a field associated with the wrong
table, or ...

Once you have the query working, you can use it as the RowSource for your
combo. The combo probably doesn't need all 8 rows here, so you can uncheck
the "Show" box under some of those columns.

There is an issue with the inner joins. If an employee has never clocked in,
they won't show in this query, and if they have clocked in multiple times
the main query will show them multiple times. I'm not clear why Clock_Table
and [Job Sheet] are needed in this query. You may be able to drop those
tables out, and just let the subquery do the work. Personally I would be
trying to get this down to just 2 columns, like this:

SELECT Employees.EmployeeID,
Trim(Employees.LastName & ", " & Employees.FirstName & " "
& Employees.MiddleName) AS EmployeeName
FROM Employees
WHERE (SELECT TOP 1 Dupe.StopDate
FROM Clock_Table AS Dupe
WHERE Dupe.EmployeeID = Employees.EmployeesID
ORDER BY Dupe.StartDate DESC, Dupe.ID DESC) Is Not Null;
[quoted text clipped - 42 lines]

ORDER BY Clock_Table.StartDate DESC, Clock_Table.StopDate DESC) Is Not
Null;

Allen

Thanks for your help. I was to get what I wanted via two queries that
are right joined and then negating the un-completed jobs which left me
with employees that are do not habe a job assigned. Below
I have my sql statement for review

SELECT Jobs_not_completed.EmployeeID AS Jobs_not_completed_EmployeeID,
Employees.EmployeeID AS Employees_EmployeeID, Employees.FirstName, Employees.
MiddleName, Employees.LastName
FROM Jobs_not_completed RIGHT JOIN Employees ON Jobs_not_completed.EmployeeID
= Employees.EmployeeID
WHERE (((Jobs_not_completed.EmployeeID) Is Null));

Thanks again for your patience

Gaetanm