How to use SQL queries in a report????

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

re: How to use SQL queries in a report????

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" <william.r.hollingsworth@boeing.com> wrote in
message news:HsvMEE.LxL@news.boeing.com...[color=blue]
> 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
>[/color]

re: How to use SQL queries in a report????

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:
[color=blue]
>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" <william.r.hollingsworth@boeing.com> wrote in
>message news:HsvMEE.LxL@news.boeing.com...
>
>[color=green]
>>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
>>
>>
>>[/color]
>
>
>
>[/color]

re: How to use SQL queries in a report????

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" <william.r.hollingsworth@boeing.com> wrote in
message news:Hsvtw1.8r2@news.boeing.com...[color=blue]
> 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:
>[color=green]
> >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" <william.r.hollingsworth@boeing.com> wrote in
> >message news:HsvMEE.LxL@news.boeing.com...
> >
> >[color=darkred]
> >>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
> >>
> >>
> >>[/color]
> >
> >
> >
> >[/color]
>[/color]

re: How to use SQL queries in a report????

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......=Select 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:
[color=blue]
>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" <william.r.hollingsworth@boeing.com> wrote in
>message news:Hsvtw1.8r2@news.boeing.com...
>
>[color=green]
>>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:
>>
>>
>>[color=darkred]
>>>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" <william.r.hollingsworth@boeing.com> wrote in
>>>message news:HsvMEE.LxL@news.boeing.com...
>>>
>>>
>>>
>>>
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>[/color][/color]
>
>
>
>[/color]

re: How to use SQL queries in a report????

Richard Holliingsworth <william.r.hollingsworth@boeing.com> wrote in message news:<HsxoFx.7K8@news.boeing.com>...[color=blue]
> In the "Record Source" property of the
> box......=Select count(node) from BL321RTM where stdverifyby = "test"
>
> Well, I've tried several versions of that and nothing will compile.
>[/color]

Richard,
have you tried:

=dcount("stdVerifyBy","tblYourTable","stdVerifyBy= '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

re: How to use SQL queries in a report????

The following union query ...

SELECT stdverifyby AS TheGroup, Count(*) AS TheCount, "Verification 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" <william.r.hollingsworth@boeing.com> wrote in
message news:HsxoFx.7K8@news.boeing.com...[color=blue]
> 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......=Select 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:
>[color=green]
> >I'm not sure that I understand what you mean by 'you can only group[/color][/color]
records[color=blue][color=green]
> >once using the GUI', Richard. You can define up to 10 grouping levels,[/color][/color]
and[color=blue][color=green]
> >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[/color][/color]
summary[color=blue][color=green]
> >query, or even subreports. I really don't have enough information to go[/color][/color]
on -[color=blue][color=green]
> >for example, what is it in your data that defines a group?
> >
> >--
> >Brendan Reynolds
> >
> >
> >"Richard Holliingsworth" <william.r.hollingsworth@boeing.com> wrote in
> >message news:Hsvtw1.8r2@news.boeing.com...
> >
> >[color=darkred]
> >>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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >>>View menu to display the Sorting and Grouping dialog box).
> >>>
> >>>Using the Count() function in a group footer section, for example,[/color][/color][/color]
would[color=blue][color=green][color=darkred]
> >>>give you a count of only the records in that group.
> >>>
> >>>--
> >>>Brendan Reynolds
> >>>
> >>>
> >>>"Richard Holliingsworth" <william.r.hollingsworth@boeing.com> wrote in
> >>>message news:HsvMEE.LxL@news.boeing.com...
> >>>
> >>>
> >>>
> >>>
> >>>>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[/color][/color][/color]
site[color=blue][color=green][color=darkred]
> >>>>or knowledge base site.
> >>>>
> >>>>Thanks for any guidance.
> >>>>
> >>>>Richard Hollingsworth
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>[/color]
> >
> >
> >
> >[/color]
>[/color]