time clock

  • Thread starter Thread starter jeff G
  • Start date Start date
J

jeff G

I am attempting to create a report that would display the
calculated time between the first entry and last entry of
a day a person makes into a datatbase. The date field is
a general format and has both date and time information.
Here is the query I am using for the report:

SELECT tblContactNotes.EmployeeID, Min
(tblContactNotes.Date) AS MinOfDate, Max
(tblContactNotes.Date) AS MaxOfDate
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
Format([date],"m/d/yy")
HAVING (((tblContactNotes.EmployeeID)=[enter initials])
AND ((DateValue([date])) Between [a] And ));

When I specify "between" values ([a] and ) that are in
2004, the query is displaying dates from 2002 and 2003 as
well as 2004. Also it is displaying mulitple entries on
some days. I would like to display only the earliest
time and latest time for each person for each day between
the date range. Also I would like another feild to
calculate the time diffeence in hours between the max and
min dates for each day.

thanks for any help.

Jeff G
 
Something like the following. Notice that I moved the criteria into where
clause from the having clause. This is more efficient in this case.


SELECT tblContactNotes.EmployeeID,
DateValue([Date]) as TheDate,
Min(tblContactNotes.[Date]) AS MinOfDate,
Max(tblContactNotes.[Date]) AS MaxOfDate,
DateDiff("n",Min(tblContactNotes.[Date]),Max(tblContactNotes.[Date])) As TotalMinutes
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
WHERE tblContactNotes.EmployeeID =[enter initials])
AND DateValue([date]) Between [a] And
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
DateDiff("n", Min(tblContactNotes.[Date]),Max(tblContactNotes.[Date]))


As for showing entries in prior years, I have no idea what is going on.
 
this info query message came up :
cannot have a aggregate function in a group by clause
DateDiff("n", Min(tblContactNotes.[Date]),Max
(tblContactNotes.[Date]))
-----Original Message-----
Something like the following. Notice that I moved the criteria into where
clause from the having clause. This is more efficient in this case.


SELECT tblContactNotes.EmployeeID,
DateValue([Date]) as TheDate,
Min(tblContactNotes.[Date]) AS MinOfDate,
Max(tblContactNotes.[Date]) AS MaxOfDate,
DateDiff("n",Min(tblContactNotes.[Date]),Max
Click to expand...
(tblContactNotes.[Date])) As TotalMinutes
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
WHERE tblContactNotes.EmployeeID =[enter initials])
AND DateValue([date]) Between [a] And
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
DateDiff("n", Min(tblContactNotes.[Date]),Max (tblContactNotes.[Date]))


As for showing entries in prior years, I have no idea what is going on.


jeff said:
I am attempting to create a report that would display the
calculated time between the first entry and last entry of
a day a person makes into a datatbase. The date field is
a general format and has both date and time information.
Here is the query I am using for the report:

SELECT tblContactNotes.EmployeeID, Min
(tblContactNotes.Date) AS MinOfDate, Max
(tblContactNotes.Date) AS MaxOfDate
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
Format([date],"m/d/yy")
HAVING (((tblContactNotes.EmployeeID)=[enter initials])
AND ((DateValue([date])) Between [a] And ));

When I specify "between" values ([a] and ) that are in
2004, the query is displaying dates from 2002 and 2003 as
well as 2004. Also it is displaying mulitple entries on
some days. I would like to display only the earliest
time and latest time for each person for each day between
the date range. Also I would like another feild to
calculate the time diffeence in hours between the max and
min dates for each day.

thanks for any help.

Jeff G
Click to expand...

.
Click to expand...
 
John -
I was able to get this query to run by eliminating the
GROUP BY in the totals and leaving it blank. I also
noticed in the dates displayed if I manually type
in "between #8/16/04# and #8/30/04#" for the criteria in
the query that the dates are correctly displayed ( no
extraneous data is displayed from 2002 and 2003). When
prompted by the query to enter the dates when the criteria
is "between [a] and " is it seeing a different format?
Any ideas on how this can be overcome?

Thanks for your help
JG
-----Original Message-----
Something like the following. Notice that I moved the criteria into where
clause from the having clause. This is more efficient in this case.


SELECT tblContactNotes.EmployeeID,
DateValue([Date]) as TheDate,
Min(tblContactNotes.[Date]) AS MinOfDate,
Max(tblContactNotes.[Date]) AS MaxOfDate,
DateDiff("n",Min(tblContactNotes.[Date]),Max
Click to expand...
(tblContactNotes.[Date])) As TotalMinutes
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
WHERE tblContactNotes.EmployeeID =[enter initials])
AND DateValue([date]) Between [a] And
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
DateDiff("n", Min(tblContactNotes.[Date]),Max (tblContactNotes.[Date]))


As for showing entries in prior years, I have no idea what is going on.


jeff said:
I am attempting to create a report that would display the
calculated time between the first entry and last entry of
a day a person makes into a datatbase. The date field is
a general format and has both date and time information.
Here is the query I am using for the report:

SELECT tblContactNotes.EmployeeID, Min
(tblContactNotes.Date) AS MinOfDate, Max
(tblContactNotes.Date) AS MaxOfDate
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
Format([date],"m/d/yy")
HAVING (((tblContactNotes.EmployeeID)=[enter initials])
AND ((DateValue([date])) Between [a] And ));

When I specify "between" values ([a] and ) that are in
2004, the query is displaying dates from 2002 and 2003 as
well as 2004. Also it is displaying mulitple entries on
some days. I would like to display only the earliest
time and latest time for each person for each day between
the date range. Also I would like another feild to
calculate the time diffeence in hours between the max and
min dates for each day.

thanks for any help.

Jeff G
Click to expand...

.
Click to expand...
 
Declare your date parameters at the beginning of the query.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1 [A] or
Select the data type of the parameter in column 2 - DateTime
Repeat for the other parameters.

Sorry 'bout the error message you got on using the aggregate in the group by. I
DO know better and I still get caught by that too darn often.

Jeff said:
John -
I was able to get this query to run by eliminating the
GROUP BY in the totals and leaving it blank. I also
noticed in the dates displayed if I manually type
in "between #8/16/04# and #8/30/04#" for the criteria in
the query that the dates are correctly displayed ( no
extraneous data is displayed from 2002 and 2003). When
prompted by the query to enter the dates when the criteria
is "between [a] and " is it seeing a different format?
Any ideas on how this can be overcome?

Thanks for your help
JG
-----Original Message-----
Something like the following. Notice that I moved the criteria into where
clause from the having clause. This is more efficient in this case.


SELECT tblContactNotes.EmployeeID,
DateValue([Date]) as TheDate,
Min(tblContactNotes.[Date]) AS MinOfDate,
Max(tblContactNotes.[Date]) AS MaxOfDate,
DateDiff("n",Min(tblContactNotes.[Date]),Max
Click to expand...
(tblContactNotes.[Date])) As TotalMinutes
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
WHERE tblContactNotes.EmployeeID =[enter initials])
AND DateValue([date]) Between [a] And
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
DateDiff("n", Min(tblContactNotes.[Date]),Max (tblContactNotes.[Date]))


As for showing entries in prior years, I have no idea what is going on.


jeff said:
I am attempting to create a report that would display the
calculated time between the first entry and last entry of
a day a person makes into a datatbase. The date field is
a general format and has both date and time information.
Here is the query I am using for the report:

SELECT tblContactNotes.EmployeeID, Min
(tblContactNotes.Date) AS MinOfDate, Max
(tblContactNotes.Date) AS MaxOfDate
FROM tblContacts INNER JOIN tblContactNotes ON
tblContacts.ID = tblContactNotes.ID
GROUP BY tblContactNotes.EmployeeID, DateValue([date]),
Format([date],"m/d/yy")
HAVING (((tblContactNotes.EmployeeID)=[enter initials])
AND ((DateValue([date])) Between [a] And ));

When I specify "between" values ([a] and ) that are in
2004, the query is displaying dates from 2002 and 2003 as
well as 2004. Also it is displaying mulitple entries on
some days. I would like to display only the earliest
time and latest time for each person for each day between
the date range. Also I would like another feild to
calculate the time diffeence in hours between the max and
min dates for each day.

thanks for any help.

Jeff G
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top