Record count in report w/o records

  • Thread starter Thread starter swansonray
  • Start date Start date
S

swansonray

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
 
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.
 
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
(e-mail address removed)
 
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)
 
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
 
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
 
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.
 
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 said:
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 said:
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
(e-mail address removed)






.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
(e-mail address removed)



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


.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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
(e-mail address removed)



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


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top