Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting records in subreports (2000)

    I have a report with 3 subreports for each of the countries in the database (subreport1, subreport2, subreport3). These are arranged across a landscape page so that I get a list of records under each report name. I have counted the records for the subsections that are on each subreport, but I would like to add all the records that appear in each of the subreports (i.e. number (subreport1_records) + number (subreport2_records) + number (subreport3_records) and present in the master report.

    Can I do this?

    kiwi44

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting records in subreports (2000)

    Does it ever happen that one of the subreports is empty?

    If so, the best way is to use the DCount function to count the records in the subreports. The exact way to do this depends on the way the subreports are linked to the main report.

    If not, you might put invisible text boxes in the report footer of each subreport, with control source

    =Count(*)

    Say that you name them Sum1, Sum2 and Sum3. You would put a text box on the main report with control source

    =SubReport1!Sum1+SubReport2!Sum2+SubReport3!Sum3

    where SubReport1 etc. are the names of the subreports as controls on the main report; these are not necessarily the same as their names in the database window.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting records in subreports (2000)

    Dear Hans,

    Thanks for the prompt response. Unfortunately, some of the countries do have null values on the subreports.

    The master report has a single field in the data source (I have tried adding 'useful' fields, but then get multiple 'records' on the report). The subreports are then linked to the master report on this field. I have tried looking at DCount, but have not been able to get this to work, yet (more homework!).

    If this is not enough information to help you guide me to a solution, please let me know what you need (the database is too complex to easily reduce to something that I could post).

    Thanks,

    kiwi44

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting records in subreports (2000)

    This is very similar to <post#=328140>post 328140</post#> but with DCount instead of DSum.

    Say that SubReport1 is based on RecordSource1 (a table or query), and linked to the main report on LinkChildField vs LinkmasterField, a number field. The number of records in the subreport is

    DCount("*","[RecordSource1]","[LinkChildField]=" & [LinkMasterField])

    If the linking fields are text fields, use

    DCount("*","[RecordSource1]","[LinkChildField]=" & Chr(34) & [LinkMasterField] & Chr(34))

    The expression for the other two is similar, with the appropriate names substituted. So the total number is

    =DCount(...)+DCount(...)+DCount(...)

  5. #5
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting records in subreports (2000)

    Wow, it's so easy when explained like that! Why can't Microsoft make it that easy? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Now I know how to make the report look like something that was put together manually in Word, and it is an instantaneous (and accurate) update of what is in the database. Very cool.

    Thanks again!

    kiwi44

Posting Permissions

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