Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    report grouped by month is skipping months (97)

    My report is grouped by month but is skipping some months altogether, as they do not appear in my tables as data. I

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    Is your report based on a query? If so, you could try an outer join from a table listing all the months to your data table. In the query grid, use the NZ function to write zeros when the field values are Null. The result should show all the months with zeros when applicable.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    This is one thing I

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    I attached a small example db. Run the report to see all months show, regardless of whether or not there is data for that month. There are two queries (one of them uses the Month function to create the link that you are having trouble with) and two tables supporting this report.
    HTH
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    thanks Thomas I checked out your dbase & followed what you meant but still have a problem, let me explain why.

    My query has dates in short date format ie 1/5/01
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    In my original attachment, please look at the query named qryCreateMonth. It uses the Month function on a ShortDate field to create a temporary Month field for each record that can be used as a link in the second query. This handles the "day of the month" issue that you are describing. Look at the table named tblMonth to see how that Month is evaluated. (The 2nd table you are describing is not the way it is done in the attachment, since you cannot include the actual day portion if this is to work correctly)

    If the report in the attachment is not what you were going for, please explain a bit further.
    I did not include the Year, but if you can see the logic in the Month portion, the Year portion could be added in a similar way. Please take another look at it and see if it makes sense to you.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    I

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    You are correct, those queries do not create records sets that can be updated.
    It would be the tables on which they are based that could be updated and those updates would then be reflected in the query results. Subsequently the report which uses it for a record source will reflect the updates as well.
    I must be missing something here ... if the records exist in the table, how does the query not being updateable cause a problem in the report? Sorry if I am being dense, I just can't understand the connection. Maybe someone else can see it.

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    well I can talk on that shortly but for now if you might clue me in on this. When I create the join properties between my tblMonths & query in the reports recordsource, the recordset created is a little more than DOUBLE what was in the query originally. I

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    The way that you describe joining the tables is the same as in my sample, so that seems correct. But my sample report didn't double the numbers, so something must be different. Do you have any other tables or queries in that query that serves as the Record Source for the report? If so, how are they joined? Look at the result of that query in the datasheet view. Each record that is selected should be assigned to a month. These are grouped in the report, so that you have 12 groups for 12 months, each with it's associated records. What exactly is being doubled ... the records (you see the same record more than once) or the value of some field within each record?

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report grouped by month is skipping months (97)

    well Thomas, put it to you this way, I finally found a good excuse to use the knowledge I got from an SQL course I took & I did this:

    SELECT tblMonth.MonthName, tblMonth.Month, tblMonth.Type, Sum(newQuery.ActivationForecast) AS SumOfActivationForecast, Sum(newQuery.TotalCommits) AS SumOfTotalCommits, Sum(newQuery.TotalPaid) AS SumOfTotalPaid, Sum(newQuery.Accrual) AS SumOfAccrual, Sum(newQuery.ProjectedSpending) AS SumOfProjectedSpending
    FROM tblMonth LEFT JOIN newQuery ON tblMonth.Month = newQuery.tempMonth
    GROUP BY tblMonth.MonthName, tblMonth.Month, tblMonth.Type;

    And got the query results as I wanted them, so I

Posting Permissions

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