Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: Moving Group & Report Counts to the Headers

    I currently have a report that is run off of one query and is grouped by a Status field and a Case field. Each Status has a number of Cases and each Case has a number of Events. Now, before the report is run, I have a form that allows the user to filter on this report by the date of the last event (eg. show me all the Cases that have events based on the last date, within the specified date range).

    I am currently using report-side calculations (using a =1 Record Source in the Group Headers) to get calculations in the Case and Status Footers. This gives me the Event counts, Case counts and Status counts. I have similarly designed counters for total counts.

    However, the counts are all in the footers. I'd like to find a way to "project" these numbers into the header somehow - to serve as an "Executive Summary" type of cut at the data. I've looked into subreports and sending the filter to the subreport. I've also tried using DCount - but I have no clue how to make it work when the filter is in place. The problem is since the filter is pivoting off of the 'Date of the Last Event', if you do any kind of aggregate count (like "group by"), you lose the date attribute and can't filter off of it. Also, I believe you can't do a Count(Distinct [attr]) in Access, so you would then need to have another query to do this type of count - making this whole Executive Summary very tedious.

    I know in Excel, you can create a formula that essentially "pulls" a value from a different cell and just reports its value. I'm trying to do something similar. Maybe make all of these counts Visible=No, but then Project them into the Header. Can this be done?

    Thanks.
    Last edited by tkchung; 10-11-05 at 11:59.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In short no:-

    the only way I can think of doing an executive header is to do another copy of the report, but with only the footers containing the summary data you want.

    Access does some tricks very well, as does Excel, as does most app software, but Access isn't Excel. I think the summary data is performed using a separate SQL query for each summary item / header and it is only triggered after the group has been recognised.

  3. #3
    Join Date
    Sep 2003
    Posts
    35
    Firstly, I find it unusually hard to believe that you can do something in excel and not in Access - I know I can't argue for that, but it seems really strange). So, I've actually been able to pass the filter string (that holds the user's date restriction range) to a DCOUNT argument successfully. I can't, however, replicate the aggregate counts (for each group). Is there any way to count unique entries in DCOUNT? If so, the problem can be solved with DCOUNT. Otherwise, I'm still clueless on this issue.

  4. #4
    Join Date
    Oct 2005
    Posts
    6

    Similar problem solved

    I'm a newbie but I've just had a similar problem. What I did was:

    Used one query as the source for the report and subreport. This query had the criteria "Is Not Null And Between [Forms]![frmYearDialog]![StartDate] And [Forms]![frmYearDialog]![EndDate]" against the date field in the query. The report called up a dialog form (frmYearDialog) which had two text boxes, StartDate and EndDate, which feeds the dates back to the query.

    The main report was grouped how I wanted it, and the subreport was grouped just on the field that I wanted to report overall. I placed the subreport in the form header. What caused me the most grief was that the subreport should have no field linkage between child and parent fields.

    For counts, just place a text field in whatever group footer you want and set the record source to "=Count([Field Name]), and make it invisible if you don't want to see it. You can then use this text box in other calculations. For example - =Round(((Sum([Quantity])/[txtTotal])*100),0) & " %". I placed the txtTotal text box in the footer, and then use it to calculate what percentage the individual totals are of the grand total.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you want to create a 'first' page executive summary then you could conceivably do it in a sub report inserted into the the report header. You could use a series of dcounts but in the medium to long term it will be a b!!d to maintain

    Just because Excel can do something and Excel and Access overlap to a certain extent it doesn't mean that what you can do in Excel can be done in Access - if the 2 prodicts were so similar then its doubtfull Microsoft would support the 2 products. Excel is great a what if, lousy at data entry and has limited reporting facilities, Access is weak in what if analysis. It dosen't have a detailed knowledge of your data, a value is not just sitting in a cell somwhere.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tkchung
    Firstly, I find it unusually hard to believe that you can do something in excel and not in Access - I know I can't argue for that, but it seems really strange). So, I've actually been able to pass the filter string (that holds the user's date restriction range) to a DCOUNT argument successfully. I can't, however, replicate the aggregate counts (for each group). Is there any way to count unique entries in DCOUNT? If so, the problem can be solved with DCOUNT. Otherwise, I'm still clueless on this issue.
    Hi

    Excel <> Access - they are different beasts used for different purposes typically by different types of people.

    No such thing as count distinct in Access - it is sadly lacking for that. If your BE is SQL Server then you could use Count(Distinct MyCol) in a view or sproc. Or else you can simulate this in SQL and pop the result into a recordset:

    Code:
      
    SELECT COUNT(*) 
    FROM (SELECT DISTINCT MyGroupCol FROM MyTable) As DerivedTable
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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