Unique value in query

[Gaetanm via AccessMonster.com]

In a combo box i need to pull down the employeeId but the combo box
shows me all the transactions on each employee verses one record.

I would like to show each employeeid that has a startdate and stopdate
that is "not null" and not show the employeeid that stopdate is null.

this is a job time clock database. the idea is you start a job by punching in
and you stop working on the job when you punch out. The job may not be
finished but your clock time is finished for that job.

I want to make sure that an employeeId is not available by making sure the
employeeid does not have a job already started and trying to start another
one.

Here is where I am:

SELECT DISTINCT Employees.EmployeeID, Employees.FirstName, Employees.
MiddleName, Employees.LastName, Clock_Table.StartDate, Clock_Table.StopDate,
Clock_Table.TotalHoursMinutes
FROM Employees INNER JOIN Clock_Table ON Employees.EmployeeID = Clock_Table.
EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.MiddleName,
Employees.LastName, Clock_Table.StartDate, Clock_Table.StopDate, Clock_Table.
TotalHoursMinutes
HAVING (((Clock_Table.StartDate)>Date()-Weekday(Date()+1)));

Any assistance would greatly be appreciated

Gaetanm

 

[Marshall Barton]

In a combo box i need to pull down the employeeId but the combo box
shows me all the transactions on each employee verses one record.

I would like to show each employeeid that has a startdate and stopdate
that is "not null" and not show the employeeid that stopdate is null.

this is a job time clock database. the idea is you start a job by punching in
and you stop working on the job when you punch out. The job may not be
finished but your clock time is finished for that job.

I want to make sure that an employeeId is not available by making sure the
employeeid does not have a job already started and trying to start another
one.

Here is where I am:

SELECT DISTINCT Employees.EmployeeID, Employees.FirstName, Employees.
MiddleName, Employees.LastName, Clock_Table.StartDate, Clock_Table.StopDate,
Clock_Table.TotalHoursMinutes
FROM Employees INNER JOIN Clock_Table ON Employees.EmployeeID = Clock_Table.
EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.MiddleName,
Employees.LastName, Clock_Table.StartDate, Clock_Table.StopDate, Clock_Table.
TotalHoursMinutes
HAVING (((Clock_Table.StartDate)>Date()-Weekday(Date()+1)));

The GROUP BY clause is getting in the way and you need to
specify the criteria to select the needed records. I'm not
sure about your data, but this should be closer to what you
want:

SELECT DISTINCT Employees.EmployeeID, Employees.FirstName,
Employees.MiddleName, Employees.LastName
FROM Employees INNER JOIN Clock_Table
ON Employees.EmployeeID = Clock_Table.EmployeeID
WHERE Clock_Table.StartDate > (Date()-Weekday(Date()+1))
AND Clock_Table.StopDate Is Not Null

 

[Bob Hairgrove]

In a combo box i need to pull down the employeeId but the combo box
shows me all the transactions on each employee verses one record.

Presumably these transactions are stored in Clock_Table, correct? It
would help enormously to have some DDL in order to see what these
tables look like...

I would like to show each employeeid that has a startdate and stopdate
that is "not null" and not show the employeeid that stopdate is null.

You have given a very concise description of what you need ... now all
you need to do is to express that in your SQL statement. For example,
there should be a "WHERE Clock_Table.StopDate IS NOT NULL" in there
somewhere.

this is a job time clock database. the idea is you start a job by punching in
and you stop working on the job when you punch out. The job may not be
finished but your clock time is finished for that job.

I want to make sure that an employeeId is not available by making sure the
employeeid does not have a job already started and trying to start another
one.

Here is where I am:

SELECT DISTINCT Employees.EmployeeID, Employees.FirstName, Employees.
MiddleName, Employees.LastName, Clock_Table.StartDate, Clock_Table.StopDate,
Clock_Table.TotalHoursMinutes

Sounds like you are storing calculated data in the "TotalHoursMinutes"
column. Since each job is actually a kind of record which won't be
likely to change once StopDate is entered, you can probably do this
safely. Just be aware that there is a potential for inconsistency here
if the StartDate and StopDate values do ever change. If all you ever
need to do is to display the value in the combo box, I would use a
formula instead of the redundant data. Reading over your description
again, it seems like an employee can work more than once on the same
job, so eventually you will need to do a sum on the total time worked
in order to bill your customer. However, you can do this just as well
by selecting the MAX(StopDate) - MIN(StartDate) and group by JobID
(where is THAT entity stored in Clock_Table, BTW? An employee will
have worked on several different jobs, hopefully...)

FROM Employees INNER JOIN Clock_Table ON Employees.EmployeeID = Clock_Table.
EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.MiddleName,
Employees.LastName, Clock_Table.StartDate, Clock_Table.StopDate, Clock_Table.
TotalHoursMinutes
HAVING (((Clock_Table.StartDate)>Date()-Weekday(Date()+1)));

First of all, you aren't aggregating anything in your query, so throw
out the GROUP BY and the HAVING clause.

Second, put the expression you now have in the HAVING section into a
WHERE section. HAVING filters records AFTER aggregation, WHERE filters
them BEFORE. This is important to remember because it might have a
great impact on performance. Besides, as we aleady know, there is no
aggregate function being used.

Third, I'm not sure why you need the criteria expression in your
HAVING clause at all if you are really interested in getting the
employees where StopDate is Null. That expression is only filtering
for the employees who started a job (and presumably finished it)
within the present week, AFAICT. What you stated is that all you need
is a list of employees who aren't currently busy with some other job.

Fourth, this is probably best done with a subquery:

SELECT EmployeeID, FirstName, <etc.>
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Clock_Table WHERE StopDate
IS NOT NULL
AND JobID = <????>);

Normally I would advise against using an IN (...) clause for
performance reasons, but since EmployeeID is probably the primary key
column of Employees, you will likely find that it performs very fast.
And if it is, you don't need SELECT DISTINCT because primary key
values must be unique.

If it doesn't perform well enough, try using what John Vinson likes to
call a "frustrated outer join" ... (try googling for that expression,
you'll certainly find something out there).

[OT] Hello John, if you're lurking! How are you?? It's been ages since
the old CompuServe MSAccess forum went defunct ... I kind of miss the
old diatribes between Joe Celko, Chris St.V, Stuart McCall, T.J.
Cardenas, etc. ... Wonder whatever happened to those guys?? Take care!
PS: feel free to drop me an e-mail at
[email protected] if you want to reminisce
further outside of the NG...
[/OT]

 

[Bob Hairgrove]

However, you can do this just as well
by selecting the MAX(StopDate) - MIN(StartDate) and group by JobID
(where is THAT entity stored in Clock_Table, BTW? An employee will
have worked on several different jobs, hopefully...)

Please ignore this bit ... I think it might actually be better to do
it the way you are already going about it, even if it means that you
are storing calculated data.

 

[Gaetanm via AccessMonster.com]

Please ignore this bit ... I think it might actually be better to do
it the way you are already going about it, even if it means that you
are storing calculated data.

Hi Marshall and Bob


I'm afraid I'm still having some difucalties.


I have two input forms on the frmStartTime form I have 2 combos
one pulls down the job# and the other pulls down the employeeId
The time is inserted in a text field via a button that has the Now command.
When the employeeid is chosen a text box is filled via a query
that gives me a DateDiff between startdate and stopdate in to total hours and
minutes for the employeeid. This text field is only for information purpases
and no calculated information is stored

The StartDate,EmployeeId and the JobId is put into a table called

On this form when I pull the employeeid I just want to get the employees
that the stopdate and the srartdate are filled and they don not have
a NULL in the Stopdate.

In other words They should not be in the pulldown combo
is they already have a job open.

The second form has employeeid combo that looks at a query
and determins is stopdate = Null for that employeeID.
And what I have here is only employeeId that Stopdate = Null
this makes my combo pick quicker.

I would like to do something like that in the first form
All employeeId have Startdate and StopDate in there records
and only some have Startdate and StopDate and Stopdate = Null
in there records. I need those.

I'm doing this for the present week that is why I have the formula
in the sql for the present week

Hope you folks can help me

Thanks

Gaetanm

 

[Marshall Barton]

Hi Marshall and Bob


I'm afraid I'm still having some difucalties.

I have two input forms on the frmStartTime form I have 2 combos
one pulls down the job# and the other pulls down the employeeId
The time is inserted in a text field via a button that has the Now command.
When the employeeid is chosen a text box is filled via a query
that gives me a DateDiff between startdate and stopdate in to total hours and
minutes for the employeeid. This text field is only for information purpases
and no calculated information is stored

The StartDate,EmployeeId and the JobId is put into a table called

On this form when I pull the employeeid I just want to get the employees
that the stopdate and the srartdate are filled and they don not have
a NULL in the Stopdate.

In other words They should not be in the pulldown combo
is they already have a job open.

The second form has employeeid combo that looks at a query
and determins is stopdate = Null for that employeeID.
And what I have here is only employeeId that Stopdate = Null
this makes my combo pick quicker.

I would like to do something like that in the first form
All employeeId have Startdate and StopDate in there records
and only some have Startdate and StopDate and Stopdate = Null
in there records. I need those.

I'm doing this for the present week that is why I have the formula
in the sql for the present week

Did you try my suggested query as RowSource for the employee
combo box on frmStartTime? If you did, what about it didn't
do what you want?

 

[Gaetanm via AccessMonster.com]

[quoted text clipped - 38 lines]

I'm doing this for the present week that is why I have the formula
in the sql for the present week

Did you try my suggested query as RowSource for the employee
combo box on frmStartTime? If you did, what about it didn't
do what you want?

Morning Marshall

I made a seperate query and executed it. It brings up All employeeId's
I even made sure and deleted stopdate enteries for this week for 3
employeeID and All employees showed up in the query.

Any ideas?

Gaetanm

 

[Marshall Barton]

However, you can do this just as well
by selecting the MAX(StopDate) - MIN(StartDate) and group by JobID

[quoted text clipped - 38 lines]

I'm doing this for the present week that is why I have the formula
in the sql for the present week

Did you try my suggested query as RowSource for the employee
combo box on frmStartTime? If you did, what about it didn't
do what you want?

Morning Marshall

I made a seperate query and executed it. It brings up All employeeId's
I even made sure and deleted stopdate enteries for this week for 3
employeeID and All employees showed up in the query.

I don't see how that could happen unless the field doesn't
really have Null in the stop date field. Are you sure the
field is a Date/Time field and not a text field with
AllowZeroLength set to Yes???

Please post a Copy/Paste of the query you used so I can
review it.

 

[Gaetanm via AccessMonster.com]

[quoted text clipped - 11 lines]

I even made sure and deleted stopdate enteries for this week for 3
employeeID and All employees showed up in the query.

I don't see how that could happen unless the field doesn't
really have Null in the stop date field. Are you sure the
field is a Date/Time field and not a text field with
AllowZeroLength set to Yes???

Please post a Copy/Paste of the query you used so I can
review it.

Marshall
here is the query

SELECT DISTINCT Employees.EmployeeID, Employees.FirstName, Employees.
MiddleName, Employees.LastName
FROM Employees INNER JOIN Clock_Table ON Employees.EmployeeID = Clock_Table.
EmployeeID
WHERE (((Clock_Table.StartDate)>(Date()-Weekday(Date()+1))) AND ((Clock_Table.
StopDate) Is Not Null));

Below First you will find a partails cut and paste of my table
Clock_Table, Blelow that I have partial paste another query which gives me
Minutes elapses via DateDiff()

JobID EmployeeID StartDate StopDate
53 2 11/14/2006 4:17:45 PM 11/14/2006 4:19:53 PM
53 1 11/14/2006 2:57:46 PM 11/14/2006 3:30:18 PM
53 1 11/14/2006 2:57:10 PM
53 1 11/14/2006 2:34:07 PM 11/14/2006 3:30:18 PM
56 4 11/14/2006 2:23:04 PM
56 1 11/14/2006 2:22:38 PM 11/14/2006 2:22:49 PM
61 3 11/14/2006 2:18:01 PM
56 2 11/14/2006 2:09:55 PM 11/14/2006 2:12:18 PM
56 2 11/14/2006 2:09:22 PM 11/14/2006 3:05:54 PM
60 2 11/14/2006 2:04:54 PM
64 3 11/14/2006 2:04:04 PM 11/14/2006 2:05:24 PM



SELECT Clock_Table.EmployeeID, Clock_Table.StartDate, Clock_Table.StopDate,
DateDiff("n",[Startdate],[Stopdate])\60 & Format(DateDiff("n",[Startdate],
[Stopdate]) Mod 60,"\:00") AS ToTalTime, DateDiff("n",[Startdate],[Stopdate])
AS TotalHoursMinutes, Round([TotalHoursMinutes]/60,2) AS HundredTime
FROM Clock_Table
WHERE EmployeeID=Forms!frmClock_Start_Table!cboEmployeeId;


EmployeeID StartDate StopDate ToTalTime
2 10/25/2006 3:38:40 PM 10/26/2006 4:12:38 PM 24:34 1474
2 10/25/2006 3:39:24 PM 10/26/2006 3:12:38 PM 23:33 1413
2 10/26/2006 11:30:42 AM10/26/2006 3:12:38 PM 3:42 222
2 10/26/2006 3:06:52 PM 11/3/2006 4:46:09 PM 193:40 11620

Hope this helps

Gaetanm

 

[Marshall Barton]

I think I am confused about which combo does what.
Shouldn't I have used this where clause?

WHERE (((Clock_Table.StartDate)>(Date()-Weekday(Date()+1)))
AND ((Clock_Table.StopDate) Is Null))
--
Marsh
MVP [MS Access]

I don't see how that could happen unless the field doesn't
really have Null in the stop date field. Are you sure the
field is a Date/Time field and not a text field with
AllowZeroLength set to Yes???

Please post a Copy/Paste of the query you used so I can
review it.

here is the query

SELECT DISTINCT Employees.EmployeeID, Employees.FirstName, Employees.
MiddleName, Employees.LastName
FROM Employees INNER JOIN Clock_Table ON Employees.EmployeeID = Clock_Table.
EmployeeID
WHERE (((Clock_Table.StartDate)>(Date()-Weekday(Date()+1))) AND ((Clock_Table.
StopDate) Is Not Null));

Below First you will find a partails cut and paste of my table
Clock_Table, Blelow that I have partial paste another query which gives me
Minutes elapses via DateDiff()

JobID EmployeeID StartDate StopDate
53 2 11/14/2006 4:17:45 PM 11/14/2006 4:19:53 PM
53 1 11/14/2006 2:57:46 PM 11/14/2006 3:30:18 PM
53 1 11/14/2006 2:57:10 PM
53 1 11/14/2006 2:34:07 PM 11/14/2006 3:30:18 PM
56 4 11/14/2006 2:23:04 PM
56 1 11/14/2006 2:22:38 PM 11/14/2006 2:22:49 PM
61 3 11/14/2006 2:18:01 PM
56 2 11/14/2006 2:09:55 PM 11/14/2006 2:12:18 PM
56 2 11/14/2006 2:09:22 PM 11/14/2006 3:05:54 PM
60 2 11/14/2006 2:04:54 PM
64 3 11/14/2006 2:04:04 PM 11/14/2006 2:05:24 PM



SELECT Clock_Table.EmployeeID, Clock_Table.StartDate, Clock_Table.StopDate,
DateDiff("n",[Startdate],[Stopdate])\60 & Format(DateDiff("n",[Startdate],
[Stopdate]) Mod 60,"\:00") AS ToTalTime, DateDiff("n",[Startdate],[Stopdate])
AS TotalHoursMinutes, Round([TotalHoursMinutes]/60,2) AS HundredTime
FROM Clock_Table
WHERE EmployeeID=Forms!frmClock_Start_Table!cboEmployeeId;


EmployeeID StartDate StopDate ToTalTime
2 10/25/2006 3:38:40 PM 10/26/2006 4:12:38 PM 24:34 1474
2 10/25/2006 3:39:24 PM 10/26/2006 3:12:38 PM 23:33 1413
2 10/26/2006 11:30:42 AM10/26/2006 3:12:38 PM 3:42 222
2 10/26/2006 3:06:52 PM 11/3/2006 4:46:09 PM 193:40 11620

 

[Gaetanm via AccessMonster.com]

I think I am confused about which combo does what.
Shouldn't I have used this where clause?

WHERE (((Clock_Table.StartDate)>(Date()-Weekday(Date()+1)))
AND ((Clock_Table.StopDate) Is Null))[quoted text clipped - 45 lines]

2 10/26/2006 11:30:42 AM10/26/2006 3:12:38 PM 3:42 222
2 10/26/2006 3:06:52 PM 11/3/2006 4:46:09 PM 193:40 11620

Marshall

The employeeId combo pulls down the EmployeeId Number
and the JobId combo Pulls down the JobId Number.

I would like some way to say that If stopdate = Null for a perticular
imployee his EmployeeId does not show in the combo box to be selected.

Gaetanm

 

[Marshall Barton]

I think I am confused about which combo does what.
Shouldn't I have used this where clause?

WHERE (((Clock_Table.StartDate)>(Date()-Weekday(Date()+1)))
AND ((Clock_Table.StopDate) Is Null))

I even made sure and deleted stopdate enteries for this week for 3
employeeID and All employees showed up in the query.

[quoted text clipped - 45 lines]

2 10/26/2006 11:30:42 AM10/26/2006 3:12:38 PM 3:42 222
2 10/26/2006 3:06:52 PM 11/3/2006 4:46:09 PM 193:40 11620

The employeeId combo pulls down the EmployeeId Number
and the JobId combo Pulls down the JobId Number.

I would like some way to say that If stopdate = Null for a perticular
imployee his EmployeeId does not show in the combo box to be selected.

Right, that's the change I made in the revised Where clause.
What's it doing now?

 

[Gaetanm via AccessMonster.com]

[quoted text clipped - 7 lines]

I would like some way to say that If stopdate = Null for a perticular
imployee his EmployeeId does not show in the combo box to be selected.

Right, that's the change I made in the revised Where clause.
What's it doing now?

Works great thanks a bunch

Gaetanm