Subquery - Summing Issue 1

[judgeh59]

Schema-
EventID
InDate
OutDate
InTime
OutTime
Status
SID "It's the employee number"
Elapsed Time "String"
SFC "Barcode Serial Number on PCB"

I have two queries that I'm trying to combine. Basically what happening is the user takes a PCB out of a nitogren cabinet and hits a button that enters the OutDate/OutTime and sets the Status to OUT. When they put the PCB back in the cabinet they hit the IN button and I set the InDate/InTime and set the Status to IN and calculate the Elapsed_Time. Yes, I realize it's not a real good practice to have calculated fields in a DB but it fixed a few issues. My problem, I have a subform that runs a query that shows SFC, OutDate, OutTime, Elasped Time. I need to be able to SUM all the elapsed times of the same SFC's. Here are the two queries and they both work independant of each other but need them to work together. So, one query sum all elapsed times by SFC on records that are completed (IE: Indate and Outdate have data). The other query SUM's records that are not complete (IE: Outdate has data and Indate is Null). Any help would be muchly appreciated....

SELECT SFC, round((now()-cdate(format(Outdate,"mm/dd/yy") & " " & format(OutTime,"hh:mm AM/PM")))*24,2) AS [ElapsedTime(Hrs)]
FROM [Exposure Time]
WHERE [Exposure Time].Status="OUT";

SELECT SFC, sum([Elapsed Time]) AS TotalET
FROM [Exposure Time]
WHERE InDate <> null
GROUP BY SFC;

Current Data Set
SFC Elapsed Time
123456 45
321654 21
987654 93
123456 20
321654 35
123456 Not calculated because it's status is set to out.

but I need to put in the subform/query the total hrs for 123456

I hope this makes sense...




Be Alert, America needs more lerts

 

[PHV]

Anyway, replace this:
WHERE InDate <> null
with this:
WHERE InDate IS NOT NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[judgeh59]

Thanks PHV...I fixed the syntax problem but still haven't been able to combine the 2 SQL Statements

Be Alert, America needs more lerts

 

[PHV]

What about this ?

SELECT SFC, SUM(IIf(Status="OUT"
,Round((Now()-CDate(Format(OutDate,"mm/dd/yy") & " " & Format(OutTime,"hh:mm AM/PM")))*24,2)
,[Elapsed Time])) AS TotalET
FROM [Exposure Time]
GROUP BY SFC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[judgeh59]

Wow, how long have you been working on this one?...just kidding, I have some testing to do, but this is looking really good....thank you Sir for your help....

Ernest

Be Alert, America needs more lerts