A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Record count in report w/o records



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 08:36 PM posted to microsoft.public.access.reports
swansonray
external usenet poster
 
Posts: 18
Default Record count in report w/o records

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
  #2  
Old May 11th, 2010, 10:08 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Record count in report w/o records

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

  #3  
Old May 11th, 2010, 10:34 PM posted to microsoft.public.access.reports
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Record count in report w/o records

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




"swansonray" wrote in message
...
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



  #4  
Old May 11th, 2010, 10:42 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Record count in report w/o records

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

  #5  
Old May 11th, 2010, 10:48 PM posted to microsoft.public.access.reports
swansonray
external usenet poster
 
Posts: 18
Default Record count in report w/o records

Hi Karl,

The SQL for two of the queries a

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

  #6  
Old May 12th, 2010, 12:11 AM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Record count in report w/o records

Build a table like this with the criteria --
Tbl_Parameters –
Count_Query
Status
Code_Low
Code_High

Put your criteria in the table like this --
Count_Query Status Code_Low Code_High
Support Active 1000 1099
Support Active 1200 1205
Support Active 1500 1512
Support Active 1700 1714
Support Active 3000 3021
Support Active 5140 5143
Support Active 6005 6006
Support Active 8000 8012
Support Active 8056 8056
Dorm_porters Active 1600 1606
Dorm_porters Active 2000 2028
Dorm_porters Active 2030 2030
Dorm_porters Active 2036 2036
Dorm_porters Active 7000 7013
Dorm_porters Active 7018 7018
Dorm_porters Active 7019 7019
Dorm_porters Active 8057 8068

If all your report data is using status of active then it does need to be in
the table but just hard written in the SQL.

Use this query to count your records --
SELECT Tbl_Parameters.Count_Query, Count(Person.WholeName) AS CountOfName
FROM Person, Tbl_Parameters
WHERE Person.Status= Tbl_Parameters.Status AND Person.AssignmentCode1
Between Tbl_Parameters .Code_Low AND Tbl_Parameters .Code_High
GROUP BY Tbl_Parameters.Count_Query;

The query results like this ---
Count_Query CountOfName
Dorm_porters 15
Support 32
etc

--
Build a little, test a little.


"swansonray" wrote:

Hi Karl,

The SQL for two of the queries a

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

  #7  
Old May 14th, 2010, 07:39 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Record count in report w/o records

This won't work if you open the report with a where condition that filters
the records displayed in a report. I almost always use code like:

Dim strWhere as String
strWhere = "[DateField] Between #" & me.txtStart & "# AND #" & Me.txtEnd & "#"
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

Using DCount() will not understand the where condition so it is very
possible the displayed value will be greater than the actual number of
records returned in the report.

--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

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




"swansonray" wrote in message
...
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



.

  #8  
Old May 15th, 2010, 02:18 AM posted to microsoft.public.access.reports
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Record count in report w/o records

Look again at the OP's post. The report is not open with a where clause.
DLookup is based on a self-contained query with it's own criteria. DLookup
will certainly work!

Steve


"Duane Hookom" wrote in message
...
This won't work if you open the report with a where condition that filters
the records displayed in a report. I almost always use code like:

Dim strWhere as String
strWhere = "[DateField] Between #" & me.txtStart & "# AND #" & Me.txtEnd &
"#"
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

Using DCount() will not understand the where condition so it is very
possible the displayed value will be greater than the actual number of
records returned in the report.

--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

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




"swansonray" wrote in message
...
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



.



  #9  
Old May 15th, 2010, 06:17 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Record count in report w/o records

I guess I provided the best answer to the wrong question. I had responded a
few days ago with a reply that assumed the OP was referencing the reports
record source there was no reply stating I was off base.

I was probably more concerned about the number of times I have seen OPs
looking for report record counts and being told that DCount() is the proper
solution when it clearly isn't.


--
Duane Hookom
MS Access MVP


"Steve" wrote in message
...
Look again at the OP's post. The report is not open with a where clause.
DLookup is based on a self-contained query with it's own criteria. DLookup
will certainly work!

Steve


"Duane Hookom" wrote in message
...
This won't work if you open the report with a where condition that
filters
the records displayed in a report. I almost always use code like:

Dim strWhere as String
strWhere = "[DateField] Between #" & me.txtStart & "# AND #" & Me.txtEnd
& "#"
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

Using DCount() will not understand the where condition so it is very
possible the displayed value will be greater than the actual number of
records returned in the report.

--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

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




"swansonray" wrote in message
...
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


.



  #10  
Old May 15th, 2010, 06:57 PM posted to microsoft.public.access.reports
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Record count in report w/o records

And my mistake .........
I said DLookup in my response back to you where I meant DCount like I
suggested to the OP.

DCount is the proper solution here!

Steve


"Duane Hookom" wrote in message
...
I guess I provided the best answer to the wrong question. I had responded a
few days ago with a reply that assumed the OP was referencing the reports
record source there was no reply stating I was off base.

I was probably more concerned about the number of times I have seen OPs
looking for report record counts and being told that DCount() is the
proper solution when it clearly isn't.


--
Duane Hookom
MS Access MVP


"Steve" wrote in message
...
Look again at the OP's post. The report is not open with a where clause.
DLookup is based on a self-contained query with it's own criteria.
DLookup will certainly work!

Steve


"Duane Hookom" wrote in message
...
This won't work if you open the report with a where condition that
filters
the records displayed in a report. I almost always use code like:

Dim strWhere as String
strWhere = "[DateField] Between #" & me.txtStart & "# AND #" & Me.txtEnd
& "#"
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

Using DCount() will not understand the where condition so it is very
possible the displayed value will be greater than the actual number of
records returned in the report.

--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

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




"swansonray" wrote in message
...
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


.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.