SQL syntax problem

[leifoet]

Given: 2 tables (BookA and BookB) connected to each other by UNION ALL and each table with the same columns: OUT and IN

Try to sum the 2 tables in 2 columns OUT and IN (OUT = BookA + BookB / IN = BookA + BookB)

The query below gives the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Thanks for tips on how to fix this error.

<%
SQL = "SELECT SUM(S_IN) AS TotIN, SUM(S_OUT) AS TotOUT FROM (SELECT SUM(Out) AS S_OUT, SUM(In) AS S_IN" & _
"FROM BookA" & _
"WHERE membersid =" & session ("uid") & "UNION ALL SELECT SUM(OUT) AS TotOUT, SUM(In) AS TotIN" & _
"FROM BookB" & _
"WHERE membersid =" & session ("uid")

 

[MarkSweetland]

You probably need to add spaces at the end of your concatenations. It's running all lines together so the syntax doesn't make sense:

SQL = "SELECT SUM(S_IN) AS TotIN, SUM(S_OUT) AS TotOUT FROM (SELECT SUM(Out) AS S_OUT, SUM(In) AS S_IN  " & _
"FROM BookA" & _
"WHERE membersid =" & session ("uid") & "  UNION ALL SELECT SUM(OUT) AS TotOUT, SUM(In) AS TotIN  " & _
"FROM BookB  " & _
"WHERE membersid =" & session ("uid")

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[leifoet]

Inserted spaces do not resolve the error.
Other tips?

 

[MarkSweetland]

You use IN and OUT as column names which could cause problems without encompassing in brackets. You also need to close and alias the last parenthesis for the initial SELECT FROM

SQL = "SELECT SUM(S_IN) AS TotIN, SUM(S_OUT) AS TotOUT FROM &_
"(" &_
"SELECT SUM([COLOR=red][[/color]Out[COLOR=red]][/color]) AS S_OUT, SUM([COLOR=red][[/color]In[COLOR=red]][/color]) AS S_IN  " & _
"FROM BookA" & _
"WHERE membersid =" & session ("uid") & "  UNION ALL SELECT SUM([COLOR=red][[/color]OUT[COLOR=red]][/color]) AS TotOUT, SUM([COLOR=red][[/color]In[COLOR=red]][/color]) AS TotIN  " & _
"FROM BookB  " & _
"WHERE membersid =" & session ("uid") & _
[COLOR=red]") as totalBook"[/color]

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[leifoet]

Thanks MarkSweetland, the query tip works perfectly.

The same file also contains this query.

SQL="SELECT 'A' AS tablenm, AAId AS BJId, Date, Description, [Out], [In] "&_
"FROM BookA "&_
"WHERE membersid =" & session ("uid") & _

"UNION ALL SELECT 'B' AS tablenm, BBId AS BJId, Date, Description, [Out], [In] "&_ 
"FROM BookB "&_
"WHERE membersid =" & session ("uid") & _
"ORDER BY Date ASC"

I think this query can be merged with below. I tried but it doesn't work - where to merge ?
Thanks for the good tip.

SQL = "SELECT SUM(S_IN) AS TotIN, SUM(S_OUT) AS TotOUT FROM &_
"(" &_
"SELECT SUM([Out]) AS S_OUT, SUM([In]) AS S_IN  " & _
"FROM BookA" & _
"WHERE membersid =" & session ("uid") & "  UNION ALL SELECT SUM([OUT]) AS TotOUT, SUM([In]) AS TotIN  " & _
"FROM BookB  " & _
"WHERE membersid =" & session ("uid") & _
") as totalBook"

 

[MarkSweetland]

I don't think they can be combined. You'll need to define what the overall intent of the query is. The first returns details of both BookA and BookB that is associated with a memberid, whereas the second returns overall totals of BookA + BookB associated with memberid regardless of the details.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach