Using SELECT COUNT in a report

[nishantd]

Hello,

Is there a way to use the COUNT functions of SQL in an Access report? For instance, I can create a report which has it's record source set to a table in SQL Server. However, I would like to use the COUNT function and assign a text box (or boxes) the return value. I have not been able to find a good way to actually do this (assign the SQL statement to the control source of a text box doesn't work either).

Additionally, I would like to perform use COUNT more than one time on the same table.

Any help appreciated,
Thanks,
Nishant

 

[Rob.Mills]

I would base the report on a query or better yet a pass-through query that performs the count.

 

[nishantd]

Rob,

I'm not sure if the basing the report on the query will work as I need to execute the count function on the same table, but many times with different WHERE clause statements.

As for the pass through, I was thinking of maybe using a View stored on the server to actually perform the queries...

Thanks,
nishant

 

[Rich]

=Count([SomeField]) or =Count(*) for the total record count

 

[nishantd]

Although I'm not in a place to test this out, can I send this function an equivalent WHERE clause? For instance:

SQL statement to count the number of rows that have the 'Value' field set to 'True':
SELECT COUNT(*) WHERE Value="True"

In a report, is the following syntax valid for the control source: =Count([Value]=True) or simply =Count([Value]).

Thank you,
Nishant