Solved

Complicated query to determine the average number of hourly transactions for each month in MS ACCESS

Posted on 2006-07-06
13
494 Views
Last Modified: 2012-06-27
I have been asked to somehow compile the average number of hourly transactions for 12 months of data broken down into months (spannig a fiscal calendar).  The time stamp in my db is AM PM formatted e.g. 12:01:44 AM.  Thinking that somehow I need to do a count for each hour and divide by the appropriate number of days in that month to get an average...any ideas...

Fiscal 2003-2004

                 Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec   Jan   Feb   Mar      
0-1am
1-2am
2-3am
3-4am
.....
10-11 pm
11-12 am
Any guidance is greatly appreciated...groovymonkey
0
Comment
Question by:groovymonkey
  • 6
  • 5
  • 2
13 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17053845
Please define an 'hourly transaction' and post the structure of your table along with a few records.  
0
 

Author Comment

by:groovymonkey
ID: 17059691
Okay...hourly transaction would be any record within the database that would fall within the appropriate time stamp e.g. 2:04:55 PM and 2:12:48 PM would be counted where the hour is 2 (or I guess 14 for the 24 hour clock)...I am trying to get a count within a month for all records that fall within each hour (e.g. 2-3pm would be the 2pm) and then to average them out.

Table structure
transaction_tm   |              transaction_dt
------------------------------------------------------
2:04:55 PM                        2003-04-01
2:12:48 PM                        2003-04-02

there are a whole bunch of other fields e.g. transaction_value however I do not have an interest in them...the table can have up to 5000 records per day.
hope this makes more sense...groovymonkey
0
 
LVL 1

Expert Comment

by:millsco
ID: 17067715
I suggest that you create a table with 12 records, 2 fields viz, month # and DaysInMonth I.e. lookup data to calculate average

Create a query that groups the records in you data table.

SELECT Hour([transaction_tm]) as intHour, Month([transaction_dt]) AS intMonth, Count([transaction_tm]) as lngCount
FROM table
GROUP BY Hour([transaction_tm]), Month([transaction_dt]);

Create a crosstab query that joins the query above to the Month table created on month #

Divide lngCount by the DaysInMonth to calculate average and output this field as Value in the crostab query design
Output intHour as Row Heading in the crostab query design
Output intMonth as Column Heading the crostab query design


0
 
LVL 44

Expert Comment

by:GRayL
ID: 17069861
So the max duration of timestamp and what if if crosses an hour boundary?
0
 

Author Comment

by:groovymonkey
ID: 17122005
GRayL...not sure what this means
So the max duration of timestamp and what if if crosses an hour boundary?...could you clarify...thanks groovymonkey
0
 

Author Comment

by:groovymonkey
ID: 17122045
Millsco,
Problem with days in month...my data spans 3 years and the February value is different.  How would I do this...Create a crosstab query that joins the query above to the Month table created on month #
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 44

Expert Comment

by:GRayL
ID: 17122715
Does your time stamp have date and time in one field or two - I sure hope the former!  In either event you need a crosstab:

TRANSFORM Count(myTable.TransactDT)
SELECT DatePart("h",myTable.TransactDT) as [Hour] FROM myTable
WHERE myTable.TransactDT BETWEEN #2003-04-01# AND 2004-03-31 23:59:59#
GROUP BY DatePart("h",myTable.TransactDT)
Pivot Format(myTable.TransactDT),"mmm") IN ("Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar")

If you have the Date and the Time in separate fields, you have to add them. Replace TransactID with:

myTable.TransactDate+myTable.TransactTime

in the five locations above.  Use your actual table name in place of myTable and your actual field(s) name(s).
0
 
LVL 1

Expert Comment

by:millsco
ID: 17125666
Not sure from your information whether you want 12 columns or 36 columns of data in your report for months. The issue for this forum is that if you understand the principles in Access you will be able to figure out the precise result you want for your self.

The table that you create is used only to lookup the # of days in a month. So if you have a different # of days in February then you will need to create a row in your table for each month that you want to look up. I.e. 3 years of data then 36 rows. You will need to create an index for these months in some fashion. Eg Jan 2006 = 0601
This table will have 2 fields viz MonthIndex, NrDaysInMonth

Create query1 on your data table as follows:
MonthIndex:Format([TimeStamp],"yy") & Format([TimeStamp],"mm")
MonthOfYear:Format([TimeStamp],"mmm")
HourOfDay:Format([TimeStamp],"hh")
CountItems:Count[Some filed that has data populated for all rows e.g. timestamp])

Group By etc..

Create Query2
Join Query1 to LookupTable on the MonthIndex Field.
Divide CountOfItems by NrDaysInMonth = AveragePerDay

Use the crosstab wizard to create a crosstab query. The datasource will be Query2.
MonthOfYear (for 12 columns) or MonthIndex (for 36 columns)  ==> Column Heading
HourOfDay ==> Row Heading
AveragePerDay (calculate Sum, First, Average etc. depending on your specific requirements) ==> Value
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 17126277
Oops, I see a typo:

TRANSFORM Count(myTable.TransactDT)
SELECT DatePart("h",myTable.TransactDT) as [Hour] FROM myTable
WHERE myTable.TransactDT BETWEEN #2003-04-01# AND #2004-03-31 23:59:59#
GROUP BY DatePart("h",myTable.TransactDT)
Pivot Format(myTable.TransactDT),"mmm") IN ("Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar");
0
 

Author Comment

by:groovymonkey
ID: 17131144
GRayL
My date time stamp is split into two fields...
transaction_dt (date)
transaction_tm (time)
how would I facilitate this in the crosstab query

Thanks groovymonkey
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17131253
Why do that?  Date and time should always be together.  If I were you, create a third field t_datetime and run this update:

Update mytable set t_datetime=transaction_dt+transaction_tm

Then remove fields transaction_dt and transaction_tm.  Rename the remaining field transactionDT.  Try to get in the habit of not using spaces, underscores and special characters in you table, field, and variable names.  Makes the troublshooting a lot easier as you will never need square brackets.

Now insert your actual table and field names into my last query.  That fixes it.  If, however, you insist on staying with separate dates and times you will have to replace each instance of transactDT in the query with:

mytable.transaction_dt+mytable.transaction_tm - and of course the correct table name.
0
 

Author Comment

by:groovymonkey
ID: 17131908
I received the db with the date and time split up...I agree with you that this is a silly thing...your code absolutely rocks...a pleasure as usual
groovymonkey
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17132037
Thanks, glad I could help.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now