Looking for a way to analyze volume per hour trends in an MS Access database
Hello experts,
I have a db that has over a million transactions for a year period. I have been tasked to analyze these transactions and determine patterns in terms of peak hours...peak days...peak weeks.
Sample Fields
-----------------
date_dt (mm-dd-yyyy)
time_dt (23:32:00)
field 1
field 2
field 3
(fields 1,2 and 3 will be relevant later when I want to determine the average number of items per transaction per hour)
I am thinking that this will be a fairly huge task if it is possible so I will start with baby steps...
Step 1
Peak hours
-------------
Logic pseudocode...break each day down into 24 hours and sum all transaction for each hour and then group by week...I guess the only way to determine patterns will be to look at the 52 weeks worth of numbers and determine if there is a pattern...unless of course someone else has done this and has a better idea...again totally out of my element here but there must be a way to do this...
I will carry the other steps into other questions with additional points once I get rolling on this one as although they are realted they stand independently of one another.
thanks for any sql starting points...groovymonkey...
I have a db that has over a million transactions for a year period. I have been tasked to analyze these transactions and determine patterns in terms of peak hours...peak days...peak weeks.
Sample Fields
-----------------
date_dt (mm-dd-yyyy)
time_dt (23:32:00)
field 1
field 2
field 3
(fields 1,2 and 3 will be relevant later when I want to determine the average number of items per transaction per hour)
I am thinking that this will be a fairly huge task if it is possible so I will start with baby steps...
Step 1
Peak hours
-------------
Logic pseudocode...break each day down into 24 hours and sum all transaction for each hour and then group by week...I guess the only way to determine patterns will be to look at the 52 weeks worth of numbers and determine if there is a pattern...unless of course someone else has done this and has a better idea...again totally out of my element here but there must be a way to do this...
I will carry the other steps into other questions with additional points once I get rolling on this one as although they are realted they stand independently of one another.
thanks for any sql starting points...groovymonkey...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Analysis by day is not do-able in a crosstab because it would produce too many columns.(365/366 required with a max available of 255)
So you would have to use a standard group-by query for this requirement.
Pete