Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: need to graph all months in sequence.

    Not sure how ro describe this but I need to display data to a graph but I don't always have data for each month. How would I show the months with no data to make the graph look correct?. The start date may vary per report. I have everything working except accounting for the no data months and getting them to show on a graph. Any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You need to create (or derive) a month table and left outer join from this to your current query. That way - you get all the months. You could use a tally table for this (a table containing a whole load of numbers from 1 up to, for example, 1000 or more).

    e.g.
    Code:
    SELECT MonthName(MyNumber) AS TheMonthName, MyNumber AS TheMonthNumber, MyData1, MyData2
    FROM (SELECT MyNumber FROM MyNumbersTable WHERE MyNumber BETWEEN 1 and 12) AS Numbers LEFT OUTER JOIN MyDataQuery ON Numbers.MyNumber = MyDataQuery.DataMonthNumber
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    159
    But what if I want the report to be the last 3 years?
    Hmm. I guesss I would have to have a table with the months AND year for all years I would expect to have to make a report.
    I saw such a table listing all weeks for the last 3 years and wondered what was the point of that but now I see.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If you make 2 tables, 1 for each month and one for each year and then create a query with the month field and the year filed but NO JOINS, Access will match each year with each month. It called something like a "full factorial" and does what you want.

    Make sure the year is sorted and then the month (number) and it will be in order.

    Example attached ...

    tc
    Attached Files Attached Files
    Last edited by tcace; 06-08-06 at 17:13.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    May 2004
    Posts
    159
    interesting take .. yes Access will bring in all unique combinations of month and year from the two tables but to get a date that can be processed in a query or graph it looks like you would have to make an expression that combines the fields and then do a cdate on the result.

    I think I will use one table that lists all possible dates and have some extra fields with the dates formatted the way I want the graph to show it and to allow a select query to set the range of dates to be used.

    Thanks for the ideas!

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What I would do is create the Month and Year tables. Join them together as tcace suggests. Then create a calulated field from each combination by adding 1 to each to get a full date. Ex.:

    Date:CDate([Month] & "/1/" & [Year])

    That will create 1/1/04, 2/1/04, 3/1/04 etc.

    Then with your date range, create calculated fields for the beginning and ending dates by stripping off the actual days and insert a 1 for the begininng of each month

    BeginRange:CDate(Month([BeginningDate]) & "/1/" & Year(BeginningDate]))
    EndRange:CDate(Month([EndingDate]) & "/1/" & Year(EndingDate]))

    Then use the BeginRange and EndRange with Between to limit the months that are selected. The final step would then be to join all of those months with the months that you actually have data.

  7. #7
    Join Date
    May 2004
    Posts
    159
    I had set it up to just use one table with all dates which was tedious to do but at least worked. However I see now that this is the slicker - read more professional - way to do it. You just have to make two tables - one with the months and one with the years. Then you just need to make a small separate query to combine the tables in an expression and cdate it. You then also put in the query criteria a reference to the fields on the form with the start and end date using a between expresion as shown above and there you are you just have to call the query to get a listing of ALL the months and years for the chart to include
    Thanks for the idea!!!

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    great!


Posting Permissions

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