DCount in Reports

A

awach

I have a report with several subreports. The report looks like:

Customer Name
Contact Info*
Product Info*
Shipping Info*

with the * being the subreports.

I want to be able to count the number of records in each subreport for the
customer on that page. The report and subreports are linked by a field
[LinkField], a autonumber assigned to each customer.

I tried doing DCount("*","[Contact Info]","[LinkField=LinkField]" in the
main report. This returns the count of all the records (without limiting to
the specified link field). I tried DCount("*", "[Contact
Info]","LinkField="Parent![LinkField]" in the subreport. That returns the
correct number so long as the count of records isn't zero. If the subreport
is null, it displays an Error message.

Is there another way I can do this? Please Help! :)
 
D

Duane Hookom

I would create several totals queries to include in your main report record
source:

SELECT [LinkField], Count(*) as NumContacts
FROM [Contact Info]
GROUP BY [LinkField];

Do the same for the other two tables and then add all three totals queries
to your main report record source and join on the LinkField. You NumContacts,
NumProducts, and NumShipping are then available in your main report's record
source.
 
Top