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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|