"Union All" help.

[MrTrue]

I'm using the ADODB text driver to query some .dat files. I want to be able to query two seperate files and merge the results into one recordset. It seems like a Union query is what I should be doing.

Individually, this works...

"SELECT a.CE, a.REMARK, b.TEAM, Count(a.REMARK) AS RmkCount "
"FROM [" & ThisFilename & "] a, [CETEAM.txt] b "
"Where a.CE = b.CE AND a.aDate >= #" & theStart & "# AND a.aDate <= #" & theEnd & "# "
"GROUP BY a.CE, a.Remark, b.TEAM "
"ORDER BY b.TEAM, a.Remark "

But when I try to use the union all I get an error saying..."You tried to execute a query that does not include the specified expression 'CE' as part of an agregate function."
Any idea what I'm doing wrong? The following is the SQL that gives me the error...

"SELECT a.CE, a.REMARK, b.TEAM, Count(a.REMARK) AS RmkCount "
"FROM [" & ThisFilename & "] a, [CETEAM.txt] b "
"UNION ALL "
"SELECT a.CE, a.REMARK, b.TEAM , Count(a.REMARK) AS RmkCount "
"FROM [" & NextFilename & "] a, [CETEAM.txt] b "
"WHERE a.CE = b.CE AND a.aDate >= #" & theStart & "# AND a.aDate <= #" & theEnd & "# "
"GROUP BY a.CE, a.Remark, b.TEAM "
"ORDER BY b.TEAM, a.Remark "

 

[r937]

the first SELECT in your UNION is missing its GROUP BY clause

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MrTrue]

Thank you, I'll give that a shot when I get into the office in the morning. It's funny, someone recommended your book to me (when I first tried working with temporary tables), and I've had a post-it in my cubicle reminding me to order it for about 4 weeks now... I just submitted my order tonight and am looking forward to reading it. I'll let you know if I'm able to get this specific statement to work tomorrow. Thanks for your help!

 

[MrTrue]

For some reason I was thinking I could get by with one "group by" clause, but your fix worked like a charm. Thank you!