Record count in report w/o records
in [Reports]
|
Prev: report parameter
Next: string field name
From: KARL DEWEY on 11 May 2010 19:11 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 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
From: Duane Hookom on 14 May 2010 14:39 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 > 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: Steve on 14 May 2010 21:18 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" <duanehookom(a)NO_SPAMhotmail.com> wrote in message news:97BC2F9C-24C3-4584-85E8-5FF33B44EA31(a)microsoft.com... > 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 >> 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 15 May 2010 01:17 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" <notmyemail(a)address.com> wrote in message news:eaOqFy88KHA.5412(a)TK2MSFTNGP06.phx.gbl... > 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" <duanehookom(a)NO_SPAMhotmail.com> wrote in message > news:97BC2F9C-24C3-4584-85E8-5FF33B44EA31(a)microsoft.com... >> 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 >>> 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: Steve on 15 May 2010 13:57
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" <duanehookom(a)gmail.com> wrote in message news:92D946DA-D392-48A4-988D-A3DD04C2B45D(a)microsoft.com... >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" <notmyemail(a)address.com> wrote in message > news:eaOqFy88KHA.5412(a)TK2MSFTNGP06.phx.gbl... >> 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" <duanehookom(a)NO_SPAMhotmail.com> wrote in message >> news:97BC2F9C-24C3-4584-85E8-5FF33B44EA31(a)microsoft.com... >>> 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 >>>> 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 >>>> >>>> >>>> . >>>> >> >> |