Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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...
  • 2
2 Solutions
Hi groovymonkey,
Am I missing somethimng here?
This sounds like a combination of something which is quite simple with something which is totally impossible (in Access)
I appreciate that you are dealing with a lot of data but that is a separate issue.

'analyze these transactions ...in terms of peak hours...peak days...peak weeks.'
This is a set of crosstab reports, one for the Hour, one for the weekday, one for the week, just counting records.
You create a simple query based on your tabel, add a column which gets the timeperiod and then use that query as the source of your crosstab.

But the bit..
... and determine patterns in terms of peak hours...peak days...peak weeks.
in my opinion is not do-able in Access.
To search for patterns you need a fully-fledged statistical analysis software package.
You could export the crosstab to XL and draw a chart but I would not regard that as 'determine patterns'.

second thought....

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.

Idea!!!...the attached link is to a free scientific data analyzer that has a standard rs232 interface and scripting language to accept data from other applications.....may you could feed weekly or monthly summarized data to the Analyzer, which has built-in regression, charting, and other analysis tools.  Worth looking into???


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now