Counting records in subreports (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

This is very similar to post 328140 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(…)

Reply | Quote

Wow, it’s so easy when explained like that! Why can’t Microsoft make it that easy?

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

Reply | Quote

Wow, it’s so easy when explained like that! Why can’t Microsoft make it that easy?

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

Reply | Quote

This is very similar to post 328140 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(…)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote