Count exclusive values in a Report

[stevepain]

Dear all,

I'm having problems with a count expression in a report. I have a series of items listed, with subdata against each one, in some cases this subdata is one rows, in others it is many rows.

Anyways, having listed these items and there subdata sets, I want a count of the number of items listed.

Unfortunately, a count on Equipment ID returns the total number of sub data items listed, as for each sub data item, the Equipment ID is associated. Is there a way of making the COUNT expression count exclusive values?

Thanks,

Stephen

 

[mhartman]

Hello:
Yes there is. You want use the Dcount function.
'
You can use the DCount function to determine the number of records that are in a specified set of records (a domain). Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control.

For example, you could use the DCount function in a module to return the number of records in an Orders table that correspond to orders placed on a particular date.

Regards
Mark

 

[stevepain]

Thanks for the suggestion with regard to DCount. I had just started looking at that as an option myself, but was sketchy on how to get the best out of this..........I then thought of a workaround.

By creating another report that only lists each bit of equipment that the report generates, and not the sub datasets that exist and putting a count in the footer of this report. I can then use this report as a subreport of the main one, and hide every value with the exception of the COUNT function of the subreport.

Appreciate it is not an ideal solution, but does what I need it to - might be useful to others in future as well?!

 

[The_Doc_Man]

If in doubt, there is always the multi-layer method.

Write a query that selects unique records based on your keys. You might have to play around a bit in the Help files to look at examples of using the UNIQUE keyword correctly, because there are levels of uniqueness.

Once you have the query set up to show only unique entries, write a count query that takes the unique query as its input rather than directly opening the table in question.