showing totals from an external query in a Report

[gguy]

I need to show a total in a report (lets call in rptA) footer for applications that have been received. The problem is there can be multiple records per application in this program. So, =count([app_no]) in the report footer gives me more apps(1001) then have been received (840).

I created a second query based on the first query and chose only the [app_no] field with totals turned on and Group By in the total line on the query, this gives me a list of the unique app numbers.

So, I created a text box in the report (rptA) footer and put =count([app_no_query]![app_no]) in the text box.

When I run the report a pop up asks me to enter an app_no.

So, Is there a way to show a total in the report footer that is from an external query?

 

[SOS]

You would have to use a DLookup to do it.

=Nz(DLookup("[app_no]", "app_no_query"),0)

 

[gguy]

The Dlookup works but it only brings in the value from the first row in the query. What I need for the report is the total record count in the app_no_query.

 

[gguy]

I created a report on the app_no_query with =count([app_no]) in the report footer then pulled this into rptA as a subreport. Thanks, GG