Hello:
Thanks for reading this.
I would like to build a report (Access 2002) that uses SQL queries to
count records in specific groups.
I've tried several methods and so far no luck.
Could someone please point me in the right direction on this one. It
dosen't have to be SQL queries, but I could not find another way to do
this with Access functions in any of my books or on the Access web site
or knowledge base site.
Thanks for any guidance.
Richard Hollingsworth 6 2020
Have you investigated the sorting and grouping feature in reports
themselves? (In report design view, select Sorting and Grouping from the
View menu to display the Sorting and Grouping dialog box).
Using the Count() function in a group footer section, for example, would
give you a count of only the records in that group.
--
Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in
message news:Hs******** @news.boeing.co m... Hello:
Thanks for reading this.
I would like to build a report (Access 2002) that uses SQL queries to count records in specific groups.
I've tried several methods and so far no luck.
Could someone please point me in the right direction on this one. It dosen't have to be SQL queries, but I could not find another way to do this with Access functions in any of my books or on the Access web site or knowledge base site.
Thanks for any guidance.
Richard Hollingsworth
OK, nice idea but.......
From what I've tried, you can only group records once using the GUI.
Fine, so I do that for the first group of records and use count() to
tally the results for that group.
Now, I need to do the same thing for 3 other groups (group bys). So,
how do I do that on a report?
Richard
Brendan Reynolds wrote: Have you investigated the sorting and grouping feature in reports themselves? (In report design view, select Sorting and Grouping from the View menu to display the Sorting and Grouping dialog box).
Using the Count() function in a group footer section, for example, would give you a count of only the records in that group.
-- Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in message news:Hs******** @news.boeing.co m...
Hello:
Thanks for reading this.
I would like to build a report (Access 2002) that uses SQL queries to count records in specific groups.
I've tried several methods and so far no luck.
Could someone please point me in the right direction on this one. It dosen't have to be SQL queries, but I could not find another way to do this with Access functions in any of my books or on the Access web site or knowledge base site.
Thanks for any guidance.
Richard Hollingsworth
I'm not sure that I understand what you mean by 'you can only group records
once using the GUI', Richard. You can define up to 10 grouping levels, and
the group header and group footer sections will be repeated for each
occurrence of that group.
It's also possible that the best solution to your problem might be a summary
query, or even subreports. I really don't have enough information to go on -
for example, what is it in your data that defines a group?
--
Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in
message news:Hs******** @news.boeing.co m... OK, nice idea but.......
From what I've tried, you can only group records once using the GUI. Fine, so I do that for the first group of records and use count() to tally the results for that group.
Now, I need to do the same thing for 3 other groups (group bys). So, how do I do that on a report? Richard
Brendan Reynolds wrote:
Have you investigated the sorting and grouping feature in reports themselves? (In report design view, select Sorting and Grouping from the View menu to display the Sorting and Grouping dialog box).
Using the Count() function in a group footer section, for example, would give you a count of only the records in that group.
-- Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in message news:Hs******** @news.boeing.co m...
Hello:
Thanks for reading this.
I would like to build a report (Access 2002) that uses SQL queries to count records in specific groups.
I've tried several methods and so far no luck.
Could someone please point me in the right direction on this one. It dosen't have to be SQL queries, but I could not find another way to do this with Access functions in any of my books or on the Access web site or knowledge base site.
Thanks for any guidance.
Richard Hollingsworth
Ok, here's the scoop.......
Lets say I have a few thousand records, say 5 thousand.
These 5K records consist of, say, 12 individual fields.
3 of those fields are...
1) stdverifyby varchar 15
2) RA varchar 4
3) RFR varchar 6
Ok, these are all text fields, not numbers.
Now, boss wants a report generating statistics on these 3 fields.
Report should look something like this.....
Verification Method:
Demonstration: 3245
Test 1345
Inspection 1245
Analysis 75
Test Case Assignments:
Richard 188
John 144
Robert 55
Jeff 200
Janet 66
Runs For Record Completions:
Failed 335
Verified 3578
Not Verified 2589
Ok, I think you get the jest.......
Now, my initial thought was to create an empty report and create 12
individual text boxes, then tie a SQL statement to each box to get the
numbers. For example.......
In the "Record Source" property of the
box......=Selec t count(node) from BL321RTM where stdverifyby = "test"
Well, I've tried several versions of that and nothing will compile.
Sum() and Dcount() won't work here because these fields are not numeric,
so the SQL approach is the only way I know to do this.
PS: I've also tried creating a VBA procedure with the SQL statements
in a Form Load event. That did'n't seem to work either.
So, any ideas???????
Much thanks for your time on this one.......
Richard H
Brendan Reynolds wrote: I'm not sure that I understand what you mean by 'you can only group records once using the GUI', Richard. You can define up to 10 grouping levels, and the group header and group footer sections will be repeated for each occurrence of that group.
It's also possible that the best solution to your problem might be a summary query, or even subreports. I really don't have enough information to go on - for example, what is it in your data that defines a group?
-- Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in message news:Hs******** @news.boeing.co m...
OK, nice idea but.......
From what I've tried, you can only group records once using the GUI. Fine, so I do that for the first group of records and use count() to tally the results for that group.
Now, I need to do the same thing for 3 other groups (group bys). So, how do I do that on a report? Richard
Brendan Reynolds wrote: Have you investigated the sorting and grouping feature in reports themselves ? (In report design view, select Sorting and Grouping from the View menu to display the Sorting and Grouping dialog box).
Using the Count() function in a group footer section, for example, would give you a count of only the records in that group.
-- Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in message news:Hs******** @news.boeing.co m...
Hello:
Thanks for reading this.
I would like to build a report (Access 2002) that uses SQL queries to count records in specific groups.
I've tried several methods and so far no luck.
Could someone please point me in the right direction on this one. It dosen't have to be SQL queries, but I could not find another way to do this with Access functions in any of my books or on the Access web site or knowledge base site.
Thanks for any guidance.
Richard Hollingsworth
Richard Holliingsworth <wi************ *********@boein g.com> wrote in message news:<Hs******* *@news.boeing.c om>... In the "Record Source" property of the box......=Selec t count(node) from BL321RTM where stdverifyby = "test"
Well, I've tried several versions of that and nothing will compile.
Richard,
have you tried:
=dcount("stdVer ifyBy","tblYour Table","stdVeri fyBy='Test'")
in the controlSource for the text box
If you want to give the name and fields of your table and a few
(<5k!) rows of the table (is there really only one table?) we may be
more help.
good luck,
Martin
The following union query ...
SELECT stdverifyby AS TheGroup, Count(*) AS TheCount, "Verificati on Method"
AS Heading
FROM tblTest
GROUP BY stdverifyby
UNION SELECT RA, Count(*), "Test Case Assignments"
FROM tblTest
GROUP BY RA
UNION SELECT RFR, Count(*), "Runs for Record Completions"
FROM tblTest
GROUP BY RFR;
.... will give you something like this ...
Analysis 3 Verification Method
Demonstration 3 Verification Method
Failed 4 Runs for Record Completions
Inspection 3 Verification Method
Janet 2 Test Case Assignments
Jeff 2 Test Case Assignments
John 3 Test Case Assignments
Not Verified 4 Runs for Record Completions
Richard 3 Test Case Assignments
Robert 2 Test Case Assignments
Test 3 Verification Method
Verified 4 Runs for Record Completions
You can use that as the record source for your report, and in the report
group by 'Heading'.
--
Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in
message news:Hs******** @news.boeing.co m... Ok, here's the scoop.......
Lets say I have a few thousand records, say 5 thousand.
These 5K records consist of, say, 12 individual fields.
3 of those fields are... 1) stdverifyby varchar 15 2) RA varchar 4 3) RFR varchar 6
Ok, these are all text fields, not numbers.
Now, boss wants a report generating statistics on these 3 fields.
Report should look something like this.....
Verification Method:
Demonstration: 3245 Test 1345 Inspection 1245 Analysis 75
Test Case Assignments:
Richard 188 John 144 Robert 55 Jeff 200 Janet 66
Runs For Record Completions:
Failed 335 Verified 3578 Not Verified 2589 Ok, I think you get the jest.......
Now, my initial thought was to create an empty report and create 12 individual text boxes, then tie a SQL statement to each box to get the numbers. For example.......
In the "Record Source" property of the box......=Selec t count(node) from BL321RTM where stdverifyby = "test"
Well, I've tried several versions of that and nothing will compile.
Sum() and Dcount() won't work here because these fields are not numeric, so the SQL approach is the only way I know to do this.
PS: I've also tried creating a VBA procedure with the SQL statements in a Form Load event. That did'n't seem to work either.
So, any ideas???????
Much thanks for your time on this one.......
Richard H
Brendan Reynolds wrote:
I'm not sure that I understand what you mean by 'you can only group
recordsonce using the GUI', Richard. You can define up to 10 grouping levels,
andthe group header and group footer sections will be repeated for each occurrence of that group.
It's also possible that the best solution to your problem might be a
summaryquery, or even subreports. I really don't have enough information to go
on -for example, what is it in your data that defines a group?
-- Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in message news:Hs******** @news.boeing.co m...
OK, nice idea but.......
From what I've tried, you can only group records once using the GUI. Fine, so I do that for the first group of records and use count() to tally the results for that group.
Now, I need to do the same thing for 3 other groups (group bys). So, how do I do that on a report? Richard
Brendan Reynolds wrote: Have you investigated the sorting and grouping feature in reports themselves ? (In report design view, select Sorting and Grouping from
theView menu to display the Sorting and Grouping dialog box).
Using the Count() function in a group footer section, for example,
wouldgive you a count of only the records in that group.
-- Brendan Reynolds
"Richard Holliingsworth" <wi************ *********@boein g.com> wrote in message news:Hs******** @news.boeing.co m...
>Hello: > >Thanks for reading this. > >I would like to build a report (Access 2002) that uses SQL queries to >count records in specific groups. > >I've tried several methods and so far no luck. > >Could someone please point me in the right direction on this one. It >dosen't have to be SQL queries, but I could not find another way to do >this with Access functions in any of my books or on the Access web
site>or knowledge base site. > >Thanks for any guidance. > >Richard Hollingsworth > > > > > This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Stephen |
last post by:
It is possible to relate queries to tables, right? It seems logical
but when I try to match my queries to any of the tables or even to
each other it gives me a blank relationship. What could I possibly be
doing wrong on this?
Thanks for the help,
Stephen
|
by: Jerry Hull |
last post by:
I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are...
|
by: Robert |
last post by:
I've got a Macro that runs a few Queries and then ends with a Report. It
opens all the Queries and leaves them open and I'd really prefer that it not
do that. Is there a way to just run the Queries and/or close them
immediately?
Thanks,
Robert
|
by: Greg Strong |
last post by:
Hello All,
Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?
From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?
Why do I ask the question? ...
|
by: john |
last post by:
I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the already existing query. I know
I can copy the report and base it on another query but then I would have to
make 10 extra reports. How can I use just one...
| |
by: wsox66 |
last post by:
I am new to Access and need some help building a report. I have looked through previous posts on reports but none of them seem to answer my question completely.
I am using Access 2003 and I have a database with one table. I have built ten different queries to organize run calculations on the records in the table. The problem is that the...
|
by: vaiism |
last post by:
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there.
If I tie all the information to a single query the report contains a full set of information for the plant for each of the people who work there. If I embed multiple queries into...
|
by: kfboren |
last post by:
I am wondering if someone can help me. I have created 10 tables, 10 queries for the tables and 10 reports from the quearies. The creteria for the queries is the field named "month". I have created one main report, which includes all 10 of the subreports. When opening the main report I have to enter the creteria month which is the same for all 10...
|
by: Brett |
last post by:
Hello,
Is it possible to have just one criteria and have it apply to a group
of queries?
I am trying to create a report with the separate results of 4 queries
based on a prompt for the user to input (only once) a date. Is this
something that I program into the report?
Also, this is related but may require a separate posting, but can I...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |