Sum issue in sql in an asp page

Hi all

I'm having a difficult time.

Here's the background. I have two tables with identical columns. TableA has sales # for today, TableB has historical sales #

I am doing an asp page and have sql statements here and there that work.

I am trying to combine a sql statement with a UNION ALL in a recordset. I'm getting the feeling I cannot Sum a sum in sql in asp.

This works:
rsSalesDenver.Source = "SELECT Sum(proddta.F554211B.SDAEXP) AS SumOfSDAEXP FROM
proddta.F554211B WHERE proddta.F554211B.SDEMCU LIKE '%DEN' Or (proddta.F554211B.SDEMCU LIKE '%FTN') AND (proddta.F554211B.SDADDJ BETWEEN
"&vjStart&" AND "&vjEnd&") UNION ALL Select Sum(proddta.F554211A.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211A WHERE proddta.F554211A.SDEMCU LIKE '%DEN' Or (proddta.F554211A.SDEMCU LIKE '%FTN');"

BUT..when I add the simple Sum to add the two sums..it just gives me a generic

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'.
/management/bi/newtest4.asp, line 393

And this is all I've added...

rsSalesDenver.Source = "SELECT Sum(SumOfSDAEXP) AS SumOfSDAEXP FROM (Select Sum(proddta.F554211B.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211B WHERE proddta.F554211B.SDEMCU LIKE '%DEN' UNION ALL Select Sum(proddta.F554211A.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211A WHERE proddta.F554211A.SDEMCU LIKE '%DEN');"


Any ideas?

remove the ; from the end of the sql statement.

And when I remove that, it simply moves the error to "syntax near ")".

This runs ok until I put in the sum of the sum. If I left out the sum of the sum it would work (but give me the wrong sum).

Maybe I'm just looking at this wrong? If I have 2 tables and I want to sum two columns in 2 different tables...would I approach it like this?

Amy

Try this

It's generally a good idea to name databases, tables and fields corresponding to their purpose.

I am assuming proddta is the servername, F554211A is the tablename and SDAEXP is the field name?

You are correct in assuming proddta is the servername, F554211A is the tablename and SDAEXP is the field name

While the code you suggested doesn't crash anymore...I don't think it's adding the two columns together, is it?

Amy