Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009

    Lightbulb Unanswered: Average number of Records by Day (Mon, Tues)

    Hello all!

    This is my first post on this forum, so please bear with me!

    I am trying to figure out how to average the number of shipments in a report by a specific day. So if I create a form with a date range, I want the report to show me that I had:

    75 average shipments on Mondays
    50 average shipments on Fridays
    22 average shipments on Saturdays

    So then based a date range, tell me the average number of shipments per day (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday).

    Any ideas how to do this in MS Access 2002?

    Thanks so much in advance!


  2. #2
    Join Date
    May 2005
    I imagine there are multiple approaches to something like this, but I would start by splitting your current query into 7 different queries, and add a WHERE criteria something like

    (Weekday(tblName.fldDate) = vbMonday)
    (Do a help search on the Weekday function for more help)

    And then of course adapt that for each of the 7 different queries (one for each day of the week).

    Hope this gives you a start!
    Me.Geek = True

  3. #3
    Join Date
    Jan 2009

    Where Clause

    Thanks for the advice!

    I am pretty new to MS Access, so I may need a little bit more direction on this.

    I understand creating the seven queries for each day of the week.

    So here is what I have done so far:

    I created a form that has fields for the 'From' date and the 'To' date, as well as a 'run report' button.

    So once the date range is stored, I need an event that will close the form and open up the report.

    As far as the record source for the fields in the report, I am confused about.

    I tried to map a report text box field to one of the queries, but it just came back with an error that said '?Name'

    I don't know what that means.

    I wanted to have one text box available in the report for each day (M - F), and then map the source of each of those fields back to each of the queries. But that still doesn't solve the 'Where' clause for the forms date range.

    Here is one of my queries I used:

    SELECT Count( AS Monday
    FROM Loads
    WHERE (((Loads.Date) Between #1/1/2008# And #1/31/2008#) AND ((Format([loads].[date],"dddd"))="Monday"));

    Thanks again!


  4. #4
    Join Date
    Jan 2007
    Provided Answers: 12
    You won't need to create 7 queries, you should be able to do it all in one; here's some pseudo code for you
    SELECT Avg([count]) As [average_shipments]
         , DatePart(weekday, date) As [day_of_week]
    FROM   (
            SELECT date
                 , Count(*) As [count]
            FROM   loads
            WHERE  date BETWEEN @parameter1 AND @parameter2
                BY date
           ) As [x]
        BY DatePart(weekday, date)
    Home | Blog

  5. #5
    Join Date
    May 2005
    Thanks for fielding that one George, I misinterpreted what you wanted from your first post, sorry Jeff.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts