[jeff87]

SELECT		KindergartenID, Kname, SUM(SumO1) AS Sum01, SUM(dum2) AS Sum02, SUM(dum3) AS Sum03
FROM			(SELECT		KindergartenID, Kname, SUM([B1&2] + [B3&4] + [B5&6]) AS SumO1, 0 AS dum2, 0 AS dum3
						  FROM			option1
						  GROUP BY KindergartenID, Kname
						  UNION
						  SELECT		KindergardenID, Kname, 0 AS dum1, SUM(B1 + B3 + B5 + B2 + B4 + B6) AS Sum02, 0 AS dum3
						  FROM			option2
						  GROUP BY KindergardenID, Kname
						  UNION
						  SELECT		KindergartenID, Kname, 0 AS dum1, 0 AS dum2, 
												   SUM(BM1 + BM2 + BM3 + BM4 + BM5 + BM6 + BI1 + BI2 + BI3 + BI4 + BI5 + BI6 + BC1 + BC2 + BC3 + BC4 + BC5 + BC6 + MA1 + MA2 + MA3 + MA4
													+ MA5 + MA6 + SC1 + SC2 + SC3 + SC4 + SC5 + SC6 + MO1 + MO2 + MO3 + MO4 + MO5 + MO6) AS Sum03
						  FROM			option3
						  GROUP BY KindergartenID, Kname) DERIVEDTBL
GROUP BY KindergartenID, Kname

when i run this query,it will get Unable to parse query...
anyone know what happen?thanks in advance.

[June7]

I don't know VB.net well but do you need the '_' line continuation character at the end of each line?

In UNION queries, declare the alias field names in the first line and do not repeat in the UNION lines. The alias declaration in first line sets the column names that the values from subsequent lines will fall under.

Also, if you build long SQLs like this one line a time with concatenation can be easier to debug. Example:

Dim strSQL As String
strSQL = "SELECT KindergartenID, Kname, SUM(SumO1) AS Sum01, SUM(dum2) AS Sum02, SUM(dum3) AS Sum03 "
strSQL = strSQL & "FROM (SELECT KindergartenID, Kname, SUM([B1&2] + [B3&4] + [B5&6]) AS SumO1, 0 AS dum2, 0 AS dum3 "
'continue for remainder of query lines

This post has been edited by June7: 01 February 2010 - 12:38 AM

[jeff87]

i do wrote it line by line,but after click debug,it will goes to the code like i posted...

[June7]

I was editing my post while you posted your next one. Review it again, maybe more helpful.

[jeff87]

thx for the reply,but i dun really know wat u mean by "UNION queries, declare the alias field names in the first line and do not repeat in the UNION lines."

and yea,i put tis query in a dataset

[June7]

'AS Sum01' is the alias field name you are creating. Do this only once within the UNION query. Ex:
SELECT x AS sum FROM table
UNON SELECT y FROM table

Your code something like

Str = "SELECT KindergartenID, Kname, SUM(SumO1) AS Sum01, SUM(dum2) AS Sum02, SUM(dum3) AS Sum03 " & _
"FROM (SELECT KindergartenID, Kname, SUM([B1&2] + [B3&4] + [B5&6]) AS SumO1, 0 AS dum2, 0 AS dum3 FROM option1 GROUP BY KindergartenID, Kname " & _
"UNION SELECT KindergardenID, Kname, 0, SUM(B1 + B3 + B5 + B2 + B4 + B6), 0 FROM option2 GROUP BY KindergardenID, Kname " & _
"UNION SELECT KindergartenID, Kname, 0, 0, SUM(BM1 + BM2 + BM3 + BM4 + BM5 + BM6 + BI1 + BI2 + BI3 + BI4 + BI5 + BI6 + BC1 + BC2 + BC3 + BC4 + BC5 + BC6 + MA1 + MA2 + MA3 + MA4 + MA5 + MA6 + SC1 + SC2 + SC3 + SC4 + SC5 + SC6 + MO1 + MO2 + MO3 + MO4 + MO5 + MO6) FROM option3 GROUP BY KindergartenID, Kname) " & _
"DERIVEDTBL GROUP BY KindergartenID, Kname"

This post has been edited by June7: 01 February 2010 - 12:50 AM