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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top