Aggregate function with Select Statement

How do I combine the following statements:

SqlJunk = " SELECT SUM(MSD+Methods+Analysis+Architectures+Models+Stud io+Elective1+Elective2+Elective3) As PersonTotals FROM tblpersons "

SqlJunk = "SELECT FirstName, LastName, WeekNo, ProgramName, StudioTeamName FROM tblpersons"

I tried UNION but didn't work

Thanx

SELECT FirstName, LastName, WeekNo,
ProgramName, StudioTeamName, 0 AS PersonTotals FROM tblpersons
UNION
SELECT "" as FirstName, "" AS LastName, "" AS WeekNo, "" AS ProgramName, "" AS StudioTeamName, SUM(MSD+Methods+Analysis+Architectures+Models+Stud
io+Elective1+Elective2+Elective3) As PersonTotals FROM tblpersons

This is assuming that your first query list statement is a summary total for the second, based on your reference to using a UNION

To use a UNION you need to same number of columns in each query and they should be the same data Type in each corresponding column.


OR

SELECT FirstName, LastName, WeekNo, ProgramName, StudioTeamName,(MSD+Methods+Analysis+Architectures +Models+Stud
io+Elective1+Elective2+Elective3) As PersonTotals FROM tblpersons

This is assuming you want together on each row.



Let me know if it was someting different that you wanted

S-

FirstName,LastName,ProgramName and StudioTeamName are text fields and the rest are numbers....so what I'm trying to do is SUM the numbers and write the (Users FirstName,LastName,......).

All this data is in one record i.e. for a certain user.....the action that a user take is to search for his record and get the totals with his/her First and lastname.....

In the meantime I'll check what you send me.

Thanx

WHEN I USE THIS STATEMENT(the one you send me)

If Request.Form("TypeSearch") = "User_Id" Then
SqlJunk = "SELECT FirstName, LastName, WeekNo, ProgramName, StudioTeamName,(MSD+Methods+Analysis+Architectures +Models+Studio+Elective1+Elective2+Elective3) As PersonTotals FROM tblpersons"
SqlJunk = SqlJunk & " WHERE User_Id=" & Request.Form("DaInBox")
End If

I GET THE FOLLOWING RESULTS

Here are the results of your search:
FirstName LastName WeekNo ProgramName StudioTeamName
Maggy Lae 1 MSE PMC
PersonTotals =

PersonTotals doesn't have a value. I want to get the SUM of all those fields.

Can you post a sample of your DB with Data?

S-

Thanx for your help....the only thing I was missing was to write out the PersonTotal value from the ResultSet to the ASP page. The way I was doing it was not writing the ResultSet thats why I was getting an empty "PersonTotals"

Thanx