Time Counting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that tells me what time a customers arrive, for example,
7:44:30 am.

I want to be able to count each time a customer arrived for each hour of the
day for an entire month, but am having troubles how to even start caluclating
this.

Please help me out.
 
Metalteck said:
I have a query that tells me what time a customers arrive, for example,
7:44:30 am.

I want to be able to count each time a customer arrived for each hour of the
day for an entire month, but am having troubles how to even start caluclating
this.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT customer_id, DatePart("h", time_column) As Hr, Count(*) As
Arrivals
FROM table_name
WHERE datetime_column BETWEEN #1/1/05# And #1/31/05#
GROUP BY customer_id, DatePart("h", datetime_column)

The time column must have a date/time data type not a string.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlcg+oechKqOuFEgEQL2PACgwptUHgWr1BYqfdl3WE7rfMKJGggAn3TR
MVat8JcRO8X0OsbAnC920/eR
=+sPF
-----END PGP SIGNATURE-----
 
I'm not sure what you mean when you say time and date column, the arrival
time is stored in on field.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In correct SQL parlance "fields" are called columns and "records" are
called rows.

A DateTime data type holds both the date and the time. Some people
design their columns to hold just the date (leaving the time portion as
midnight: 00:00:00), or just the time (leaving the date portion as the
ZERO date: December 30, 1899), or both date and time. A DateTime value
is stored as a Double number. The integer part is the number of days
since December 30, 1899. The decimal part is the number is the fraction
of the day: Midnight is .0 and Noon is .5.

When the time data is stored as a DateTime data type time, calculations
can be performed on the values. If the data type is a string data type
(TEXT in Access) then calculations cannot be performed on the value,
unless it is converted to a DataTime data type.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlrb7YechKqOuFEgEQLSagCeP4awNxS+/7SMjDdBGTw6d6g7KrQAoJ9R
W3P/w6mEcN2IYkulKbTmj2oK
=fhJW
-----END PGP SIGNATURE-----
 
When I run the SQL statement, this is the result.

Arrived Hr Arrivals
2/1/2005 7:03:29 AM 7 1
2/1/2005 7:03:56 AM 7 1
2/1/2005 7:53:17 AM 7 1
2/1/2005 7:54:10 AM 7 1
2/1/2005 8:24:34 AM 8 1
2/1/2005 8:25:38 AM 8 1
2/1/2005 9:15:25 AM 9 1
2/1/2005 9:33:08 AM 9 1

This will give me each time for each day for an entire month. I guess, what
I want is it to break down so that know how many people showed up at the 7am
hour for 1/1/05, how many showed up for the 8am hour for 1/5/05 without
having to manually count it. for each day. Because after everything has been
summed up, I need to total everything for each hour of the month. I was
wondering if using a crosstab query would make this easier. Sorry for making
it more complex than it has to be.
 
I figured a way to get around it by creating a form and using the count
feature.

I have another question with regards to this query. I need to know the time
frame of the arrival of the customer to the time he was seen by a
representative. For example, if a customer arrives at 7:55 am and is seen at
8:05 am. The time frame is 10 minutes. I have a field that is named seen that
has all of these times. I know that access has a comand called timelapsed,
but don't know if that is the command i need to use.

Please help
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've never heard of "timelapsed." There is a DateDiff() function that
can give the difference in Hours, Minutes, or Seconds between two
date/times. E.g. (debug window):

? DateDiff("n", #07:55:00#, #08:05:00#)
10

"n" is used as the minutes indicator, to avoid confusion w/ the months
indicator "m."

Therefore, the query could use:

SELECT Arrived, Seen, DateDiff("n", Arrived, Seen) As ElapsedTime
FROM ...

=====

Your previous post about getting the count of Arrivals per hour per day:

SELECT DateValue(Arrived) As ArrivalDate,
DatePart("h", Arrived) As ArrivalHour,
Count(*) As Arrivals
FROM ...
WHERE ...
GROUP BY DateValue(Arrived), DatePart("h", Arrived)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlxKH4echKqOuFEgEQL9GgCcDSiuSTjmPE6HbRPfmmiwQFfgqgsAoPZk
yAaPfSVHthNmlndaphSK4ATC
=uDgo
-----END PGP SIGNATURE-----
 
Back
Top