Results 1 to 10 of 10
  1. #1
    Join Date
    May 2005
    Posts
    9

    Unanswered: Link between date field and month

    I'm using Microsoft chart (ugh) in an Access report. The underlying query is of events by date, and is charted by month. Some months have no events, though. In the chart I'd like to show all months, even if they have no events.

    How do I do it? I thought of adding a table with months 1-12 - but I can't link these months to the date.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure you can

    give some details about your two tables and i'll help you write the sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    The table of months will work if you include a 'date part' in your query. In a new column in the query you need a heading of Month: DatePart(["m", [EventDate]). This then gives you the month number of the event date so that you can set up the relationship to your table of months on an outer join.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can also use the Month function.

  5. #5
    Join Date
    May 2005
    Posts
    9
    Quote Originally Posted by r937
    sure you can

    give some details about your two tables and i'll help you write the sql
    Thanks, Rudi

    The table in question is simple:
    EventID (autonumber)
    EventDate (Date/time)
    EventType (text)
    Event (text)

    So far, there are events occurring in July, August and October, but I'd like the chart to show all months. My work around was to create another table, with a key field of
    Month (Number, 1-12)

    My plan was to link the month to the date in the event table as an outer join, but of course the simple link doesn't work - none of the dates are 1 - 12...

    I'd be happy to see any ideas you have!
    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are definitely on the right track with the LEFT OUTER JOIN

    could you fill me in a bit on what you'd like to pass to the chart? should there be one row for every day in the year? or one row for each month? does there have to be a date? or just a month number? and how is the event data passed? if you're passing only one row per month, what if a month has more than one event?

    if you could show sample data for what you want the query to produce, that would help
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2005
    Posts
    9

    What's in a chart?

    Thanks. The chart is a normal column chart with five categories representing five regions. In SQL, the basic query looks like this:

    PARAMETERS [Enter required year] Text ( 255 );
    SELECT DISTINCTROW DatePart("m",[EvDate]) AS [Month], tblEvent.Region, Count(tblEvent.EvID) AS Events, DatePart("yyyy",[EvDate]) AS [Year]
    FROM tblEvent
    WHERE (((tblEvent.EvDate) Is Not Null))
    GROUP BY DatePart("m",[EvDate]), tblEvent.Region, DatePart("yyyy",[EvDate])
    HAVING (((DatePart("yyyy",[EvDate]))=[Enter required year]));

    This returns a table of months and regions, with a count of events for each month and region. They drop neatly into my chart, but only for the months when there were events. Quiet months disappear... but I'd like to show the months where nothing happened. As you can see, the chart is shown for a single year selected in the query, so will only need 12 months.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's assume you have a table called Months with a single column called mm, with 12 rows, containing the numbers 1 through 12...
    Code:
    PARAMETERS [Enter required year] Text ( 255 );
    SELECT tblEvent.Region
         , [Enter required year] as [Year]
         , Months.mm             as [Month]
         , Count(tblEvent.EvID)  AS Events
      FROM Months
    left outer
      join tblEvent
        on (
           DatePart("m",tblEvent.EvDate) = Months.mm
       and DatePart("yyyy",tblEvent.EvDate) = [Enter required year]
           )
    GROUP 
        BY tblEvent.Region
         , Months.mm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2005
    Posts
    9
    Very nice! Thanks, R937...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    happy to help
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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