Prev: report parameter
Next: string field name
From: swansonray on 11 May 2010 15:36
Hi all,

In the detail section of a report I want to display the number of records a
query returns.

Example query named "support"
In the report I want to display Support = "number of records in query"
and then continue with the number of records in other querys with different
names.

Thank you for your assistance.

Ray Swanson
Lemoore, CA
From: KARL DEWEY on 11 May 2010 17:08
You could make each one a subreport.

You could use a union query to combine the individual queries and add a
field to identify data.

You could combine your queries into a single query with a calculated field
for each result.

If you want some ideas of doing the latter then post the SQL of a couple of
your queries.

--
Build a little, test a little.


"swansonray" wrote:

> Hi all,
>
> In the detail section of a report I want to display the number of records a
> query returns.
>
> Example query named "support"
> In the report I want to display Support = "number of records in query"
> and then continue with the number of records in other querys with different
> names.
>
> Thank you for your assistance.
>
> Ray Swanson
> Lemoore, CA
From: Steve on 11 May 2010 17:34
Hello Ray,

DCount("*","NameOfYourQuery") will give you the number of records in
NameOfYourQuery. Add an unbound textbox to your report and put =
DCount("*","NameOfYourQuery") in the control source.

Steve
santus(a)penn.com



"swansonray" <swansonray(a)discussions.microsoft.com> wrote in message
news:2145F04E-3F15-498E-80E2-202535AF61A5(a)microsoft.com...
> Hi all,
>
> In the detail section of a report I want to display the number of records
> a
> query returns.
>
> Example query named "support"
> In the report I want to display Support = "number of records in query"
> and then continue with the number of records in other querys with
> different
> names.
>
> Thank you for your assistance.
>
> Ray Swanson
> Lemoore, CA


From: Duane Hookom on 11 May 2010 17:42
If the "number of records a query returns" refers to the report's record
source then a simple text box with a control source of:
="Number of records in this report: " & Count(*)
will work.

If the report returns no records then there will be no Detail Section. You
would need a text box in the Report Header like:
="Number of records in this report: " & IIf(HasData,Count(*) ,0)

--
Duane Hookom
Microsoft Access MVP


"swansonray" wrote:

> Hi all,
>
> In the detail section of a report I want to display the number of records a
> query returns.
>
> Example query named "support"
> In the report I want to display Support = "number of records in query"
> and then continue with the number of records in other querys with different
> names.
>
> Thank you for your assistance.
>
> Ray Swanson
> Lemoore, CA
From: swansonray on 11 May 2010 17:48
Hi Karl,

The SQL for two of the queries are:

Support query:

SELECT Person.CDC1, Person.WholeName, Person.Status, Person.Classification,
Person.AssignmentCode1
FROM Person
WHERE (((Person.Status)="Active") AND ((Person.AssignmentCode1) Between
"1000" And "1099" Or (Person.AssignmentCode1) Between "1200" And "1205" Or
(Person.AssignmentCode1) Between "1500" And "1512" Or
(Person.AssignmentCode1) Between "1700" And "1714" Or
(Person.AssignmentCode1) Between "3000" And "3021" Or
(Person.AssignmentCode1) Between "5140" And "5143" Or
(Person.AssignmentCode1) Between "6005" And "6006" Or
(Person.AssignmentCode1) Between "8000" And "8012" Or
(Person.AssignmentCode1)="8056"))
ORDER BY Person.AssignmentCode1;

Dorm porters query:

SELECT Person.CDC1, Person.WholeName, Person.Status, Person.Classification,
Person.AssignmentCode1
FROM Person
WHERE (((Person.Status)="Active") AND
((Person.AssignmentCode1) Between "1600" And "1606" Or
(Person.AssignmentCode1) Between "2000" And "2028" Or
(Person.AssignmentCode1)="2030" Or (Person.AssignmentCode1)="2036" Or
(Person.AssignmentCode1) Between "7000" And "7013" Or
(Person.AssignmentCode1)="7018" Or (Person.AssignmentCode1)="7019" Or
(Person.AssignmentCode1) Between "8057" And "8068"))
ORDER BY Person.AssignmentCode1;

All of the data to be extracted from Person table. Some of the records to be
counted contain one value in a field like Person.Classification = A1 SWL

Sample SQL
SELECT Person.CDC1, Person.WholeName, Person.Status, Person.Classification,
Person.AssignmentCode1
FROM Person
WHERE (((Person.Status)="Active") AND (Person.Classification)="A1 SWL"

I only want to display the number of records that = A1 SWL not the records
themselves. The same for the querys.

Ray Swanson
Lemoore, CA


"KARL DEWEY" wrote:

> You could make each one a subreport.
>
> You could use a union query to combine the individual queries and add a
> field to identify data.
>
> You could combine your queries into a single query with a calculated field
> for each result.
>
> If you want some ideas of doing the latter then post the SQL of a couple of
> your queries.
>
> --
> Build a little, test a little.
>
>
> "swansonray" wrote:
>
> > Hi all,
> >
> > In the detail section of a report I want to display the number of records a
> > query returns.
> >
> > Example query named "support"
> > In the report I want to display Support = "number of records in query"
> > and then continue with the number of records in other querys with different
> > names.
> >
> > Thank you for your assistance.
> >
> > Ray Swanson
> > Lemoore, CA
 |  Next  |  Last
Pages: 1 2
Prev: report parameter
Next: string field name