embedded SELECT in query

Does anyone know the correct syntax for this:

SELECT  [Col a] AS [a], [Col b],
            name, SELECT SUM(amountTime) FROM myUps AS Expr1 .......


I have tried putting square brackets around the SELECT SUM(...  but that did not work either.
ARACK04Asked:
Who is Participating?
 
hongjunCommented:
try this?

SELECT  [Col a] AS [a], [Col b], name, (SELECT SUM(amountTime) FROM myUps) AS Expr1 ...
0
 
rockiroadsCommented:
use round brackets

select [col a] as a, [col b], [name], (select sum(amountime) from myups ....)

0
 
rockiroadsCommented:
or u can just use DSUM

select [col a] as a, [col b], [name], DSUM("amounttime","myups","where clause")

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hongjunCommented:
mine not working?
0
 
rockiroadsCommented:
thanks for the pts/grade :-D
I noticed hongjun posted the same but just beat me, if my 1st post worked, did his work? if so, a share of the spoils one thinks :)
0
 
hongjunCommented:
A split would be more appropriate.
0
 
rockiroadsCommented:
hongjun, my sql is very similar to yours, the only visible difference that I can see is Name. I had wrapped it in square brackets because NAME itself is a reserved word in MS Access. Its not to say the SQL wont work without them, Im just saying that is a possibility.

0
 
ARACK04Author Commented:
I meant to split it, sorry - I selected the wrong two comments - my apologies.  Working with Acces is making me very stressed out,  oh how I miss the comfort of SQL Server
0
 
ARACK04Author Commented:
I'll re-post the quest - hongjun, you can answer it, and I'll award you the points.  Fair?
0
 
hongjunCommented:
You should post a zero-point question in http://www.experts-exchange.com/Community_Support/ and state you decision to amend the split. A moderator / admin will reopen this question.

You should not be posting a new question.

hongjun
0
 
rockiroadsCommented:
I did wonder cos Im sure hongjun's code should work, I was just trying to guess why u may not of accepted it.
Now we know :)
I fully understand how stressful access can be, how I miss MySQL!!!
0
 
rockiroadsCommented:
ARACK04
there is a limit of max 500 pts per question
What u have to do is request from community support a reopen of this question so that u can allocate points accordingly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.