Time Counting

MS Office Access


  • 1. Query to find best of most recent 3 speed ratings by date
    Hi, I calculate speed ratings for horse racing in the UK. My database now contains over 100,000 ratings. I am a bit of a novice at Access and have been exporting ratings from the database earned in the last few months and doing a lot of manual sorting in excel to reduce the no. of ratings for each race to a sensible number. I would like to be able to select the best of the last 3 ratings for each horse using queries/macros. I have tables for horses, speed ratings, declarations for the next day, etc. I would very much appreciate some assistance with this. I will happily send anyone a sample database. (simplified) (I tried to attach it to a message, but the message dissapeared!) Pete.
  • 2. Format Function in Query Field Doesn't Work
    Hello, I have successfully created a field in a crosstab query that joins two pieces of information. However no matter what I try I can't get the format function to apply to the field. Here is the string: Rank & Result: First([Rank] & Chr(13) & Chr(10) & IIf ([endofweekresult]>0,Format ([endofweekresult],"#,##0.00%"),Format ([endofweekresult],"0.00%[Red]"))) The percent format does work however the [Red] color won't apply itself. ??? Any help would be appreciated.
  • 3. Ranking Inside a Querry
    Thanks to Garry Miller for his assistance last night with my first set of questions. I am brand new to Access, so I apoligize if I ask questions that have been asked before. In a database of soccer statistics, I have all the statistics from every season in one table. I wrote a querry ranking the players totals for a paticular catagory from highest to lowest. I would like to insert a column to the left of the players name called "Rank" that assends from 1 to XX (xx=number of players in this catagory) so that when the querry is returned it looks like this: Rank PlayerName GP 1. Tatu 557 2. Powers 459 3. Smith 434 4. David 400 4. Jones 400 etc. Is such a thing possible? If it is, would it be possible for Access to recognize the same data in a field and have the rank be a tie (in the example above, two players each playing 400 games would have a rank of 4)? Thank you in advance for your help and patience with a newcommer to the program! Alan Balthrop Team Historian Dallas Sidekicks Indoor Soccer Club XXXX@XXXXX.COM

Time Counting

Postby TWV0YWx0ZWNr » Sun, 10 Apr 2005 05:33:07 GMT

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 

Please help me out.

Re: Time Counting

Postby MGFoster » Sun, 10 Apr 2005 09:25:26 GMT

Hash: SHA1

Something like this:

SELECT customer_id, DatePart("h", time_column) As Hr, Count(*) As
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)

Version: PGP for Personal Privacy 5.0
Charset: noconv


Re: Time Counting

Postby TWV0YWx0ZWNr » Wed, 13 Apr 2005 03:44:02 GMT

I'm not sure what you mean when you say time and date column, the arrival 
time is stored in on field.

Re: Time Counting

Postby MGFoster » Wed, 13 Apr 2005 05:19:43 GMT

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)

Version: PGP for Personal Privacy 5.0
Charset: noconv


Re: Time Counting

Postby TWV0YWx0ZWNr » Thu, 14 Apr 2005 00:39:02 GMT

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.

RE: Time Counting

Postby TWV0YWx0ZWNr » Thu, 14 Apr 2005 06:12:15 GMT

I figured a way to get around it by creating a form and using the count 

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

Re: Time Counting

Postby MGFoster » Thu, 14 Apr 2005 07:23:14 GMT

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#)

"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 ...
GROUP BY DateValue(Arrived), DatePart("h", Arrived)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Version: PGP for Personal Privacy 5.0
Charset: noconv


Similar Threads:

1.HAVING / Where time count / excluding any instance

Greetings this almost New Year's Eve,

I have a set of data with open and close dates (for one person).  I want to 
calculate the duration the person was open.  But I can't do [close] - [open] 
because some of the closures are errors--you can identify an error if the 
next open date is the next month.  For example, 5/16/2000 closures is an 
error--because reopened 6/27/2000.  So really, I want to subtract 1/1/2000 
date from final closure of that period--which is 4/25/02

Open	Close
2/15/1990	4/20/1992
8/1/1995	11/12/1995
1/1/2000	5/16/2000
6/27/2000	12/7/2001
1/4/2002	4/25/2002
3/14/2004	10/17/2004
6/4/2005	11/30/2005

Any thoughts on how to do this?
Here's what I've tried:

1) labeling closures as "bad" or "good" -- if "open date - 30 days < closure 
date", then label closure date as "bad".  Then I have an array--
5/16/2000 good
5/16/2000 good
5/16/2000 bad    ---showing the one time where it closed & reopened
5/16/2000 good

and I'd need to then do a query eliminating a date that has _any_ "bad" 
labels (I just submitted a question about this, since my HAVING clause isn't 
working properly)
and final step -- subtract open date from smallest close date that is "good".

Any thoughts on a faster/better way to do this?  (such as saying "if there's 
_any_ open date that's up to 30 days > than the close date, then don't use 
that close date in the time duration calculation.

Thank you.

2.Count person only 1 time and show person not counted

A manager needs to sit with each of their employees once a quarter.  In the 
past, we have said if a manager has 8 employees, they need to have 8 sit 
alongs each quarter.  

The problem with this is, it does not take into account that a manager may 
sit with one person two times and not at all with another employee.  So the 
manager has 8 sit alongs, but actually only sat with 7 employees.  How can I 
build a query to only count a person 1 timer per quarter and to also show a 
person on the same report that did not get a sit along.

The report for this should list the manager and all of his employees under 
him, like this.

Manager            Qtr 1   Qtr2  Qtr 3  Qtr 4  YTD
  Employee1         1        1       0        1       3
  Employee2         0        0       0        0       0
  Employee3         1        1       1        1       4

3.how to get count(col1), count(col2), count(sol3) with only one query

Hallo everybody!
I have in a table three columns with boolean values. So I can get the number 
in a column:
SELECT count(col1)
FROM tbl where col1=true

Is it possible to get count(col1), count(col2), count(sol3) with only one 
query? In every
column should be considered only true values (col1=true, col2=true, 
Thanks for your ideas!

4.Counting how many times a button is pressed

Hello All,
I have a form that has a command button on it that prints a letter for the 
record that it is on.  
What the user wants is a report that shows how many times the button was 
clicked within a specified date range.

I am thinking that I need to do an event on the ON Click for the button.  
But how do I do that?  Do I need to have a seperate table to store the date 
and a number then tell a query to count it for the report? 

Any help would be great to get me on the right track.

5.counting time

I have a table with 3 "time" fields:
1 starttime
1 stoptime
1 "worked time"

I created a querry to calculate the "worked time" (simple: 

Finally I have to sum all "worked time"s together
I do this with following SQL:

     sql = "SELECT Sum(Uren.gewerkt) AS SomVanUur FROM Uren WHERE 
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "DD/MM/YYYY") 
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat, 
"DD/MM/YYYY") & "# ;"
     Set kost = db.OpenRecordset(sql)

Then I enter the value in an entryfield:

     Me!rek = Format(kost!somvanUur, "hh:mm")

the somvanUur is 1,229166
Me!rek shows 05:30 while it should be 28:30

What am I doing wrong?


6. Counting num chars in real time

7. count number of times a form is displayed

8. real time character count

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 50 guest