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

Please define an 'hourly transaction' and post the structure of your table along with a few records.  

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

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

So the max duration of timestamp and what if if crosses an hour boundary?

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

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 #

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

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

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

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.

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

Thanks, glad I could help.