Zero value in query (1 Viewer)

VincentSp

New member
Local time
Today, 03:03
Joined
Feb 10, 2020
Messages
12
Hi,

I have a query which calculate the total hours per job. These hours are split in working hours and downtime.
But in case of only working hours I want the downtime hours filled with a "0" .
I found a couple of threads on this topic but can't figure out how to use the code correctly.

SQL:
SELECT QryStatusReport3a.JobNumber, First(QryStatusReport3a.SumOfHoursWorkedTotal) AS FirstOfSumOfHoursWorkedTotal, QryStatusReport3b.SumOfHoursWorkedTotal
FROM QryStatusReport3a INNER JOIN QryStatusReport3b ON QryStatusReport3a.JobNumber = QryStatusReport3b.JobNumber
GROUP BY QryStatusReport3a.JobNumber, QryStatusReport3b.SumOfHoursWorkedTotal;
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:03
Joined
Sep 21, 2011
Messages
14,048
I would have thought even if you had working and downtime in the same record, the default would be zero on a new record.?
If it is Null use the NZ() function.?
 

plog

Banishment Pending
Local time
Yesterday, 21:03
Joined
May 11, 2011
Messages
11,613
There's things I question about every clause of your query:


SELECT - you should never use 'First' its a horrible function that doesn't do what you expect it to do.

FROM - if there are no records from QryStatusReport3b then you need to JOIN QryStatusReport3b to QryStatusReport3a via a LEFT JOIN. This will make sure every record in QryStatusReport3a shows in this new query.

GROUP BY - I don't understand why this is an aggregate query. I don't think this clause should be here at all. My guess is you are using this as a hack to eliminate duplicates that you can't figure out how to get rid of any other way.



With all that said, I think you should start over. I do not think this new query should be built from the 2 existing queries but from the underlying data. Can you post a database that has all the necessary tables and then QryStatusReport3a and QryStatusReport3b in it?
 

VincentSp

New member
Local time
Today, 03:03
Joined
Feb 10, 2020
Messages
12
Thank you for your feedback I revised the query as shown below.
The database itself has a jobnumber, start/end time, total time and a check box (true = working hours / not true =down time).
The first query filters the total down time per job based on the checkbox value "False" and the second query filters the total working hours per job based on the checkbox value "True".
 

VincentSp

New member
Local time
Today, 03:03
Joined
Feb 10, 2020
Messages
12
Forgot the SQL code :unsure:

SELECT QryStatusReport3a.JobNumber, QryStatusReport3a.SumOfHoursWorkedTotal, QryStatusReport3b.SumOfHoursWorkedTotal
FROM QryStatusReport3a INNER JOIN QryStatusReport3b ON QryStatusReport3a.JobNumber = QryStatusReport3b.JobNumber;
 

Users who are viewing this thread

Top Bottom