SQL Help

[bells]

I have the following SQL statment

strSql = "Select myTable.Region, SUM(myTable.Q1) as q1, Sum(myTable.Q2) as q2 FROM myTable GROUP BY region"

'then open the rs

myRs.open strSql, myConnection,adOpenKeyset, adLockOptimistic

now the everything above is Ok
the objective is to extract the value of Q1, Q2 grouped by region
i have 4 regions

Therefore
myRs!q1 'gives me the value for the first region

my Question is how do i get the value for the second region not
the second question q2.

in general what i want is

Q1 Region1 value
Q1 Region 2 value.....

I knnow i can use Do..Loops and Movenext
but the problem is that i need to use this
to show it in a dataReporter where i need to mention
a field name of that recordset. if i put a text box in a datareporter and type q1 in the dataField property of that text box then i see the first record for the first region
but i can not display for the other regions.
any suggestion is appreciated.
thanks

 

[SBendBuckeye]

Hello,

Try this:

SELECT myTable.Region, SUM(myTable.Q1) AS q, 1 AS Indicator
FROM myTable GROUP BY Region
UNION
SELECT myTable.Region, SUM(myTable.Q2) AS q, 2 AS Indicator
FROM myTable GROUP BY Region
ORDER BY Indicator, Region

You may need to use column numbers instead of names with the Union statement in the Order By (eg Order By 3, 1).

Have a great day!

j2consulting@yahoo.com