Query ALL

[pipestew]

I am querying the sum of delay time per job. The query successfully returns these sums, but does not return a value for a job if no delays occured. How do I make the query generate "0" for jobs which contained no delays.

 

[tehNellie]

nz([column/Calculation],"0") will replace NULL values in your query with 0.

 

[ajetrumpet]

what is the SQL??

 

[pipestew]

SELECT DISTINCTROW DailyProduct.ProdID, Sum(DELAYTimePerDelayID.TotalDelayTime) AS [Sum Of TotalDelayTime]
FROM (DailyProduct INNER JOIN DELAYTimePerDelayID ON DailyProduct.ProdID = DELAYTimePerDelayID.ProdID) INNER JOIN (DelayReasons INNER JOIN Delays ON DelayReasons.DelayReasonID = Delays.DelayReasonID) ON (DailyProduct.ProdID = Delays.ProdID) AND (DELAYTimePerDelayID.DelayID = Delays.DelayID)
WHERE (((DelayReasons.DelayType)="Mechanical"))
GROUP BY DailyProduct.ProdID
HAVING (((Sum(DELAYTimePerDelayID.TotalDelayTime))=nz([column/Calculation],"0")));


Sum Of TotalDelayTime: TotalDelayTime is the "expression" that is summing the delays per job. Does the nz function need to substitute that expression or does it need to be places somewhere else in the query?

 

[ajetrumpet]

Pipe, to answer your question of why you cannot get a "0" as a total in your query if there are no delay times in the group....

Lets replace this...

SELECT DISTINCTROW DailyProduct.ProdID, 
Sum(DELAYTimePerDelayID.TotalDelayTime) AS [Sum Of TotalDelayTime]
FROM (DailyProduct INNER JOIN DELAYTimePerDelayID ON 
DailyProduct.ProdID = DELAYTimePerDelayID.ProdID) INNER JOIN 
(DelayReasons INNER JOIN Delays ON DelayReasons.DelayReasonID = Delays.DelayReasonID) 
ON (DailyProduct.ProdID = Delays.ProdID) AND (DELAYTimePerDelayID.DelayID = Delays.DelayID)

WHERE (((DelayReasons.DelayType)="Mechanical"))

GROUP BY DailyProduct.ProdID

HAVING (((Sum(DELAYTimePerDelayID.TotalDelayTime))=nz([column/Calculation],"0")));[/quote]

with THIS...

SELECT DailyProduct.ProdID, 
IIF(Sum(DELAYTimePerDelayID.TotalDelayTime) > 0, Sum(DELAYTimePerDelayID.TotalDelayTime), 0) 
AS [Sum Of TotalDelayTime]

FROM (DailyProduct INNER JOIN DELAYTimePerDelayID ON 
DailyProduct.ProdID = DELAYTimePerDelayID.ProdID) INNER JOIN 
(DelayReasons INNER JOIN Delays ON DelayReasons.DelayReasonID = Delays.DelayReasonID) 
ON (DailyProduct.ProdID = Delays.ProdID) AND (DELAYTimePerDelayID.DelayID = Delays.DelayID)

WHERE (((DelayReasons.DelayType)="Mechanical"))

GROUP BY DailyProduct.ProdID;

Hopefully, that one works for you. I was also under the impression that tried using DISTINCTROW to elimante duplicate records for the same job. If that was the purpose, you don't need it. The SUM function, coupled with GROUP BY does this for you.

 

[pipestew]

Unfortunately, that code produced the exact same results.

I am still only receiving results from ProdIDs during which mechanical delays occured. Hmmm. I have no idea what to do.

 

[ajetrumpet]

Well,

Without a file to look at, I cannot say for sure what else should be done. You are welcome to post it if you like.

If my SQL produced the same result as yours, I would be inclined to think that the problem does not lie with the query code...

 

[pipestew]

I have included all of the necessary tables/queries. Thanks in advance if you take the time to look it over.

 

[pipestew]

Forgot to attach.

 

[ajetrumpet]

Stew,

Sorry for the delay in getting back to you. Anyway...

I have fixed your query. All you needed to do was drop the inner join... I subsituted the LEFT OUTER JOIN instead. This function will give you ALL records of the LEFT (or "source") table, even if it has no related records in the joining table.

Even though this is probably what you want to see, I wouldn't continue doing things this way... I would basically house the ID records that don't have delays in a separate query until you need them, but it's up to you. To house the records with no delays....

SELECT [ProdID]
FROM [DailyProduct]
WHERE [ProdID] NOT IN (SELECT [ProdID] FROM Delays);

You will also see in your query, that I have Converted the autonumbers to integers (CInt), and written IIF statements for the "Delay table" fields that are listed. If you don't write the IIF's, you will see "#Error" in the query field where there is no related record for the ID in the "Delay" table.

One thing to note is the word "none" I used to indicate that there is no delay for the ProdID. If you use a "STRING" with the IIF statment's "TRUE" section, the entire column will convert to a string (i.e., numbers will align left instead of right). If you don't want this, you will have to use a numeric field type for the "TRUE" section, as you cannot mixed data types in columns.

Also, take note of the small SUBQUERIES that had to be included in the IIF statements (to determine ProdID's presence in the Delay table).

Actually, the only ID I found that did not have a delay associated with it, was # 63. Sounds like the machines are doing OK!!

 

[pipestew]

Excellent work. I wish I could fully comprehend all the work you did, but unfortunately, my knowledge does not allow me to do so. Anyways...It is CLOSE to working perfect. The DELAYTimePerDelayID Query works perfect. However, the Mechanical query is still slightly off. The mechanical query needs to show ALL ProdID's, and show the sum of the mechanical delays that occured within that ProdID. The Mechanical Delays are listed in the DelayReasons Table and associated with the DelayID found in the TimePerID Query. Sorry for the confusion. If you make all of this work, you will be my new hero. Thanks a ton.

 

[ajetrumpet]

Stew,

In the file you will find a few other minor changes, but only field name changes. Also, look at the query grids for both the queries, and you will find that the tables included have been changed. This is because, in order to query records that are in a joined table of the table you need data from, that table has to present in the query (and joined with the SQL statement), although you may not need to SELECT any fields from it.

Try to follow what I did, as I'm pretty sure this would be "tip of the iceberg" sort of stuff to a professional programmer.

I think you pretty much know what you're doing, because before you even started a thread, you were able to query DATEDIFF and a FORMAT change to reflect the Delay Time in Hours, which, to me is pretty impressive.

This might have been easier for me too, because I can say to you...
"You want a total of the machine downtimes that were mechanically related, right??",

whereas a programmer might say...
"To get a field total of a variant, specify your table joins in the grid appropriately and use a conversion funcion to obtain the proper data type and output"

Anyway, I hope everything works out for you!!