Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    11

    Day of Week Totals

    I have a table that has Event, Start day and End day as some of the fields. I would like to have a query that totals the number events for each day of the week. The tricky part is that the start day and end day may be different and I need to count the totals on both days.

    Example:

    Sample Data in table:

    Event/Start Day/End Day
    1/Mon/Mon


    2/Mon/Tues
    3/Mon/Wed
    4/Wed/Fri
    5/Sun/Thu

    I would like the query to produce the following:

    Day/No. Events
    Sun/1
    Mon/4
    Tue/3
    Wed/3
    Thu/2
    Fri/1
    Sat/0

    Any help you can provide would be greatly appreciated.

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    This is easy to do if you have dates stored in your db for the start/stop of each event:

    1. Set up a table with the dates you want to output (call it tblDates for this example)

    2. Run a query like this:

    SELECT tblDates.TheDate, (SELECT Count(*) FROM tblEvents WHERE tblDates.TheDate >= tblEvents.StartDate AND
    tblDates.TheDate <= tblEvents.EndDate) AS EventsThatDay
    FROM tblDates
    WHERE tblDates.TheDate BETWEEN [Enter start date] AND [Enter end date];

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 AM
  2. When Week Numbers Collide!
    By Schwagr in forum Queries
    Replies: 3
    Last Post: 03-18-2006, 10:06 PM
  3. Week number wrong
    By Nikki17 in forum Queries
    Replies: 6
    Last Post: 03-18-2006, 10:01 PM
  4. query - totals
    By mslieder in forum Access
    Replies: 0
    Last Post: 02-22-2006, 06:11 PM
  5. query problem, oppointments for one week
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-18-2006, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums