Looking for a way to analyze volume per hour trends in an MS Access database

Posted on 2006-06-05
Last Modified: 2006-11-18
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...
Question by:groovymonkey
    LVL 77

    Assisted Solution

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

    LVL 77

    Expert Comment

    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.

    LVL 38

    Accepted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    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 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…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 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

    21 Experts available now in Live!

    Get 1:1 Help Now