merge multiple queries in a report

[bfamo]

Hi guys,

I'm going to make a report which will contain a lot of results. Instead of running a query for each result, I want to have one big query that covers all results (this is more effective... or?)

How can I put these two queries into one?

        qry = "SELECT Count(*) AS CountResult01 " & _
         " FROM TblScore WHERE " & _
         " [Score1] = 30 "

    rs.Open qry, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Me.Result01.Caption = rs!CountResult01

    rs.Close



        qry = "SELECT Count(*) AS CountResult02 " & _
         " FROM TblScore WHERE " & _
         " [Score1] = 40 "

    rs.Open qry, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Me.Result02.Caption = rs!CountResult02

    rs.Close

Thanks!

 

[HarleyQuinn]

One way would be to UNION them:

SELECT '30' as [Scores], Count(*) AS CountResult01
FROM TblScore WHERE [Score1] = 30
UNION
SELECT '40' as [Scores], Count(*) AS CountResult01
FROM TblScore WHERE [Score1] = 40 etc...

You can then populate your counts based on the value of the [Scores] column.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bfamo]

Is there a way to use the lable names such as "Me.Result01.Caption", "Me.Result01.Caption"... etc.

I have already given names to all the lables in the report, and now all that remains is the code in VB. Maybe not the best way to do it, but...

 

[HarleyQuinn]

I'm not aware of a way that you can do what you want (if I indeed understand correctly).

What you can do is just filter the recordset for each value of [Scores] in turn and then set the label's caption property equal to CountResult01

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

Why not simply use a single aggregate query like this:
SELECT Score1, Count(*) AS CountResult
FROM TblScore
GROUP BY Score1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

PHV - Doh! Don't know how I decided to over complicate things...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Thanksfully the rest of my posts are still valid in this situation

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.