SELECT TECH_ASSIGNED,
LOG_ID,
SUM(HOURS_ESTIMATED) as SHOURS,
(SELECT SUM(HOURS_WORKED)
FROM TRACK
WHERE TRACK.LOG_ID = LOG.LOG_ID) AS WHOURS
FROM LOG
WHERE FINISH_DT Is Null
AND APPROVAL='A'
GROUP BY TECH_ASSIGNED
You had what looked like a select statement that was selecting multiple
items in your where clause and that will cause problems as that select can
only return a single column, not multiples as you where doing.
Ray Thompson
Systems Administrator
Tau Beta Pi Association www.tbp.org
The Engineering Honor Society
Integrity and Excellence in Engineering
_____
From: NANCY GRAHAM [mailto:[EMAIL PROTECTED]
Sent: Monday, January 19, 2004 4:49 PM
To: SQL
Subject: Mathematics
I have two tables LOG, TRACK. The LOG contains LOG_ID, TECH_ASSIGNED and the
number of HOURS_ESTIMATED to complete the log . The TRACK table keeps
HOURS_WORKED, LOG_ID and TECH_ASSIGNED.
I need the hours assigned to each tech and the hours actually worked by each
tech. Then I need to find the hours remaining to be worked by each tech by
subtracting HOURS_WORKED FROM HOURS_ESTIMATED.
I have this working in ACCESS with a query of a query. How do I get it to
work in SQL in CF5.0?
SELECT TECH_ASSIGNED, LOG_ID SUM(HOURS_ESTIMATED) as SHOURS
FROM LOG
WHERE LOG_ID IN
(
SELECT LOG_ID, SUM(HOURS_WORKED) AS WHOURS
FROM TRACK
WHERE LOG_ID = LOG_ID
)
AND FINISH_DT Is Null AND APPROVAL='A'
GROUP BY TECH_ASSIGNED
Thanks for any assistance.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
