Calculating hourly total

[biswajit.tamu]

Hi,

I have data like this
03/14/05 13:09:42.0 0 CON
03/14/05 13:10:19.0 1 CON
03/14/05 13:10:21.0 2 CON
03/16/05 00:57:42.0 3 CON
03/16/05 03:14:21.0 4 CON
03/16/05 03:14:21.5 5 CON
03/19/05 16:54:57.0 6 CON
03/19/05 16:59:42.0 7 CON
03/19/05 17:04:36.5 8 CON
03/19/05 17:06:42.0 9 CON
03/19/05 17:09:22.0 10 CON
03/19/05 17:11:31.5 11 CON
03/19/05 17:11:32.0 12 CON
03/19/05 17:13:38.0 13 CON
03/19/05 17:13:38.5 14 CON
03/19/05 17:16:46.5 15 CON
03/19/05 17:20:21.0 16 CON
03/19/05 17:20:21.5 17 CON
03/19/05 17:22:19.0 18 CON
03/19/05 17:25:09.5 19 CON
03/19/05 17:25:10.0 20 CON
03/19/05 17:27:57.0 21 CON
03/19/05 17:27:57.5 22 CON

I want to write a query which will calculate the hourly total number of
events for a particular 'CON.

I.e th eresult looks like

3/19/04 1 4 CON
3/19/04 2 5 CON
3/19/04 3 5 CON
..
..
..
..
3/19/04 24 7 CON

i.e this gives the hourly count of the events for a particular date and
CON..

I am really in need. Please help.

Thanks
BJ

 

[Tom Ellison]

Dear BJ:

SELECT MyGroup, DateValue(DateTime) AS MyDate, Hour(DateTime) AS MyHour,
COUNT(*) AS Ct
FROM BJ
GROUP BY MyGroup, DateValue(DateTime), Hour(DateTime)

The above does something like what you want. Fix up the table and column
names with your actual names.

Tom Ellison

 

[biswajit.tamu]

Thanks a lot dude. You are fast. Wihout this, I would have wasted lot
of time.
Thanks a lot again Tom.

 

[Tom Ellison]

Dear BJ:

This does not show all hours of every day, only those with some activity.
If you want the one that shows all 24 hours of every day, let me know.

Tom Ellison

 

[biswajit.tamu]

Tom,

Hope I am not asking you too much

Well, I have similar requirement for monthly total and yearly total for
the Groups as above.
Will this query work ? I am just starting to know access.

You help is much appreciated.

Also, is there any function which will round the time to the nearest
second given the data is like this:

08/28/04 10:07:27.0 0 BOW
08/28/04 10:07:31.5 1 BOW
08/28/04 10:07:56.5 2 BOW
08/28/04 10:07:58.5 3 BOW
08/28/04 10:08:05.5 4 BOW
09/06/04 09:05:51.5 5 BOW
09/06/04 09:16:32.5 6 BOW
09/06/04 09:22:22.0 7 BOW
09/06/04 09:28:20.0 8 BOW
09/06/04 09:38:56.5 9 BOW
09/06/04 09:41:46.0 10 BOW
09/06/04 09:42:53.5 11 BOW
09/06/04 09:43:51.5 12 BOW
09/06/04 09:45:15.0 13 BOW
09/06/04 09:47:49.5 14 BOW
09/06/04 09:52:15.5 15 BOW
09/06/04 09:55:12.5 16 BOW
09/06/04 09:57:17.5 17 BOW
09/06/04 09:58:47.0 18 BOW
09/06/04 09:59:55.0 19 BOW
09/06/04 10:00:34.5 20 BOW
09/06/04 10:01:06.5 21 BOW
09/06/04 10:01:31.5 22 BOW
09/06/04 10:01:54.0 23 BOW
09/06/04 10:02:15.5 24 BOW
09/06/04 10:02:35.0 25 BOW
09/06/04 10:02:57.5 26 BOW
09/06/04 10:03:19.5 27 BOW
09/06/04 10:03:41.0 28 BOW
09/06/04 10:04:03.0 29 BOW


Thanks In Advance

BJ

 

[biswajit.tamu]

Actually,

Yes, I want to show for all 24 hours.
Could you please help.

I have another requirement, which will populate 2 more tables from this
data with Hourly and monthly totals. The source is same table as given
below.
08/28/04 10:07:27.0 0 BOW
08/28/04 10:07:31.5 1 BOW
08/28/04 10:07:56.5 2 BOW
08/28/04 10:07:58.5 3 BOW
08/28/04 10:08:05.5 4 BOW
09/06/04 09:05:51.5 5 BOW
09/06/04 09:16:32.5 6 BOW
09/06/04 09:22:22.0 7 BOW
09/06/04 09:28:20.0 8 BOW
09/06/04 09:38:56.5 9 BOW
09/06/04 09:41:46.0 10 BOW
09/06/04 09:42:53.5 11 BOW
09/06/04 09:43:51.5 12 BOW
09/06/04 09:45:15.0 13 BOW
09/06/04 09:47:49.5 14 BOW
09/06/04 09:52:15.5 15 BOW
09/06/04 09:55:12.5 16 BOW
09/06/04 09:57:17.5 17 BOW
09/06/04 09:58:47.0 18 BOW
09/06/04 09:59:55.0 19 BOW
09/06/04 10:00:34.5 20 BOW
09/06/04 10:01:06.5 21 BOW
09/06/04 10:01:31.5 22 BOW
09/06/04 10:01:54.0 23 BOW
09/06/04 10:02:15.5 24 BOW


You have been really nice.

Thanks
BJ

 

[Tom Ellison]

Dear BJ:

For yearly totals, just use the Year() function instead of DateValue and
drop the Hour() function part.

For monthly totals, use Year() and Month() functions in place of DateValue()
and Hour().

To round the time, decompose the whole thing: Year, Month, Day, Hour,
Minute, and Seconds. Round the seconds and put it all back together with
DateSerial. Not so nice. Probably, make your own function to do this.

Tom Ellison

 

[Tom Ellison]

Dear BJ:

You will need two tables added.

One would contain all possible dates. This is so you would have results for
any dates where there is no data for that "Group". You only need one column
in this table, but you must add many rows, one for each day.

Another would have values 0 to 23 for the 24 hours in a day.

The query would be more complex. I'll work on that and get back.

Tom Ellison

 

[biswajit.tamu]

Hey, Thanks again.

I am also trying to implement yoru suggestion.
I would be grateful, if you can send me your code, if you are done
before me..

Thanks
Biswajit Jena

 

[Tom Ellison]

Dear Biswajit Jena:

Here is a query that is working here:

SELECT BJx.MyGroup, D.ADate AS MyDate, H.Hr AS MyHour,
(SELECT COUNT(*)
FROM BJ BJ1
WHERE BJ1.MyGroup = BJ.MyGroup
AND DateValue(BJ1.DateTime) = D.ADate
AND Hour(BJ1.DateTime) = H.Hr)
AS Ct
FROM (SELECT DISTINCT MyGroup FROM BJ) BJx, AllDates D, Hours H
ORDER BY BJx.MyGroup, D.ADate, H.Hr

This is based on your table plus two more tables I explained before. One is
AllDates with one column ADate. The other is Hours with one column Hr. You
may be using different table and column names. Fix these throughout the
query.

I use aliases D and H. Initially, please do not change these.

If you have trouble, post back what you have for a query and what the
trouble is.

Tom Ellison

 

[biswajit.tamu]

Tom,

I was thikning ,
instead of populating 2 more tables with the Date and Hour, is it
possible to have the one you did earlier to show the hourly total for
the days where there is some activity, and then another query which
will give me all the dates and hours where there is no activity i.e
count is 0 ...
Then I might add them.
The how woudl it be..

What do you think ?

BJ

 

[Tom Ellison]

Dear BJ:

The days and hours have to come from somewhere. A simple and classic
solution is a table.

What you propose does nothing to remove this requirement that I can see.

What objection do you have to doing it this way?

Tom Ellison

 

[biswajit.tamu]

No no . I dont have any objection though.

I proposed this, because I thought , it would save us populating one
more table ( Date ) . Eventually, my aim is to build a access
application to do data analysis.

So I was just trying to save one more step of populating this dates
table from the Data. Hour table is fine as its only going to hold 0-23.

But date i think we have to populate as and when we have data .

What I understand is, we have to populate the date table from the
original data , Am right Tom ?

If this is the case, in case of the join with the date table, can I use
a SELECT query which does nothign but returns the dates which I would
have got from the Date table ?

What do u think ?
Thanks for your time Tom. I am really grateful.

BJ

 

[biswajit.tamu]

Tom,

I have another requirement which is bugging me is time shifts,

like on a particular day, if one record is recorded at say 12:12:34 as
9
and then another event at say 12:11:23 as 3

08/28/04 10:07:27.0 0 BOW
08/28/04 10:07:31.5 1 BOW
08/28/04 10:07:29.5 2 BOW -------Look here . This is an
error. I got to find this.
08/28/04 10:07:58.5 3 BOW
08/28/04 10:08:05.5 4 BOW

Also, if in the set of the data,
if I have a large time shift, like

08/28/04 01:07:27.0 0 BOW
08/28/04 01:07:31.5 1 BOW
08/28/04 09:07:29.5 2 BOW -------Look here .This is an huge
shift. I got to find.
08/28/04 09:07:58.5 3 BOW
08/28/04 09:08:05.5 4 BOW

Can it be done in query ?

Thanks in advance,
Hey Tom, I will take you to dinner man

BJ

 

[Tom Ellison]

Dear BJ:

You have a point. If every date you need is in the original data, you can
use that as the source for dates. Use a SELECT DISTINCT to get this. It
should work well.

However, if there is a day skipped then you won't see that in the results.
That's the only likely drawback.

Tom Ellison

 

[BJ]

Hi Tom,

Need your help again.

I have a table with Autonumber as PK.

Say like this
Col-1(PK) Col-2 Col-3
1 03/21/2006 "Text1"
2 05/23/2006 "Text2"


I want to insert a new record into it from my access application.
How do I get the PK value so that I can enter the new record ?

Thanks in advance

BJ

 

[Tom Ellison]

Dear BJ:

Autonumbers are automatically assigned by the database. You cannot
generally choose them.

When you insert a row, just ignore the autonumber column. It will be filled
in automatically. There's nothing you need to do.

I have a strong feeling this isn't what you mean. I'm going to guess you
want to assign the autonumber so it comes between 1 and 2.

Well, autonumbers don't work that way. It is very common to use autonumbers
as a "crutch" and expect them to do things they don't do.

There's really no substitute for natural keys to sort things the way you
want. With respect to any reasonable sorting of the data, ignore the
autonumber column and put it in the order you want.

Tom Ellison

 

[BJ]

Tom,

No no. Thats what I meant. I mean the forst answer is true. I just
need to insert records to that table. SO I was thinking I probably have
to do a MAX value, take that value, add 1 to it which becoes my next
auto number. And then I insert. Well, thats easy then.

Tom, I have one question, I have a table like this :
F1 F2 F3
08/03/05 14:35:30.0 186 BOW
08/03/05 14:38:24.0 187 BOW
08/03/05 14:41:38.5 188 BOW
08/03/05 14:45:46.0 189 BOW
08/03/05 14:47:19.5 190 BOW
08/03/05 14:48:21.0 191 BOW
08/03/05 14:49:14.5 192 BOW
08/03/05 14:49:15.0 193 BOW
08/03/05 14:50:13.0 194 BOW

No primary Key in this table.

How do I find the duplicate records and delete all of them just keeping
one..

Thanks
BJ

 

[Tom Ellison]

Dear BJ:

I believe it looks as though you have an endless supply of questions!

OK, a query like this will expose the duplication:

SELECT F1, F2, F3, Count(*) AS Ct
FROM YourTable
GROUP BY F1, F2, F3
HAVING COUNT(*) > 1

This will identify, but not eliminate them.

This query will show each one only once:

SELECT DISTINCT F1, F2, F3
FROM YourTable

Create a new table to contain the unduplicated values. Insert the results
of the above into this.

On the new table, you should put a unique index on all columns. It will
then not permit duplicates to be added.

Any part of your application that tries to duplicate will then error. These
parts of the application should be changed so they do not do this.

Tom Ellison

 

[BJ]

Hey Tom,

I am really sorry.l know I asked you lot of questions.
I am new to Access and face lot of problems.

You have been really helpful. I am learning hard. Reading books.
I have a long way to go. I have to build a application using access.

I am also working on some activex components.

I am really grateful to you. You have been so helpful.

Thanks
BJ