Newbie with Null value problem (1 Viewer)

[Feeble Goat]

I'm stuck, I'm an Access newbie with no VB knowledge. Please help.

Here is the SQL of the Query in question:

SELECT [Production Run].[Production Run ID#], [Production Downtime Query].[Downtime Sum] AS [Downtime Hours], [Production Run Details].[Crew Size], [Downtime Hours]*[Crew Size] AS [Total Hours Down]
FROM ([Production Run] LEFT JOIN [Production Downtime Query] ON [Production Run].[Production Run ID#] = [Production Downtime Query].[Production Run ID#]) INNER JOIN [Production Run Details] ON [Production Run].[Production Run ID#] = [Production Run Details].[Production Run ID#];]​

Here is the issue: If there is no Downtime with a Production Run ID#, there is no Downtime Sum record. What is the proper expression to return a 0.00 to the Downtime Hours field when the Downtime Sum record does not exist?

Thanks.

 

[gasbored]

I have the same query. Any ideas Please?

I also want a query to record a count of zero, for example my monthly report query reads something like:

Select system_area, count(*)
from tbl_support_issues

Result should be something like:
Downtime (Planned ) 2
Downtime (unplanned) 0
Sofware releases 4

But if the count for Downtime (unplanned) is zero, this is not shown.

How to display a count of zero??

 

[Brianwarnock]

Feeble Goat

Firstly blanks in object names are a pain, they lead to syntax errors and stop access helping you.

We really needed to see your [Production Downtime Query] as that is probably where you need to generate the 0 hours it will need to be something like


SELECT prodtable.productionrunid, Sum(nz([downtime],0)) AS dt
FROM downtimetable RIGHT JOIN prodtable ON downtimetable.productionrunid = prodtable.productionrunid
GROUP BY prodtable.productionrunid;

Gasbored
You didn't give enough info to make sense to me, I suspect you cut your SQL too much.
Brian

 

[gasbored]

here are my exact queries

Thanks for your reply, BW

First I run this query called support_stats_for_monthly_report

SELECT report_category, count(*) AS instances
FROM ISSUES_BY_SYSTEM_AREA
GROUP BY report_category;

This is the query called ISSUES_BY_SYSTEM_AREA
SELECT tbl_requests.request_id, tbl_requests.vendor_ir_raised, tbl_requests.vendor_ir_no, tbl_requests.system_area, tbl_system.report_category, tbl_requests.date_opened, tbl_requests.date_closed
FROM tbl_requests INNER JOIN tbl_system ON tbl_requests.system_area = tbl_system.system_area
WHERE (((tbl_requests.date_opened)>=[Start Date] And (tbl_requests.date_opened)<=[End Date]) AND ((tbl_system.system_area)=[tbl_requests].[system_area]));

basically this is a support database where we enter records manually and also import reords from our vendor's support database. Each record has a request_id (the primary key) and may also have a vendor_ir_no.

I hope this gives you more info

 

[Brianwarnock]

I know I'm getting old,and maybe I'm finally losing it but I cannot see the link between those 2 queries. However if the non zero result is from the first then sure how can you get a result if the system does not know a category exists.
You need to feed in all of the Report_categories, from a table of them, and with the correct join you will get 0 for those not present in ISSUES_BY_SYSTEM_AREA.

Brian

 

[neileg]

Joins are the key to this.

An INNER join (type 1 in Access terms) returns data only when there are matching records on both sides of the join.
An OUTER join either LEFT or RIGHT (type 2 or 3 in Access terms) returns all the records from one side and any records that match from the other side.

So when you've done the Count(*) and you know that there may be some nulls, you need to use an OUTER join to ensure that all records are returned even when there is a null.

 

[Brianwarnock]

I've just noticed that Feeble Goat's post was March 2004, I don't think he/she will be back for the reply , but why did gasbored piggyback on a 3 year old post, especially as it was different.

He/she caused me to waste my time

Brian

 

[Rabbie]

Brian, at least Gasbored had searched the forum.

 

[Brianwarnock]

Brian, at least Gasbored had searched the forum.

True ,very true, we b*&&$^s are never satisfied are we?

Brian

 

[gasbored]

Thanks for your time and sorry for wasting it

I did a search and found the phrase "I also want a query to record a count of zero" in FG's post. This, I found useful, because I wanted a query to record a count of zero !

If Brianwarnock would condescend to remember when (s)he was less knowledgeable, (s)he might understand.

As I said, thanks for your time on this.

 

[Brianwarnock]

IIf Brianwarnock would condescend to remember when (s)he was less knowledgeable, (s)he might understand.

As I said, thanks for your time on this.

True ,very true, we b*&&$^s are never satisfied are we?

Brian

This was meant to be an acknowledgement that I was perhaps hasty, oh BTW it wasn't you I wasted my time on but answering Feeble goat

Brian (male last time I looked)