UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Display "0" in query results when there are no records present to query    
 
   
mark909
post Oct 2 2009, 05:48 AM
Post #1

UtterAccess Addict
Posts: 117



How would i get this query to display a value of zero if there are no records for CABLE_POINT.CABLE_POINT_TYPE ="Break Out Splice Point" And CABLE_POINT.CAPACITY="12"


SELECT Count(CABLE_POINT.CABLE_POINT_TYPE) AS [No of 12f Loop Joint]
FROM CABLE_POINT
GROUP BY CABLE_POINT.PROJECT_ID, CABLE_POINT.CABLE_POINT_TYPE, CABLE_POINT.CAPACITY
HAVING (((CABLE_POINT.PROJECT_ID)=Forms!frmReqForm!txtProject_ID) And ((CABLE_POINT.CABLE_POINT_TYPE)="Break Out Splice Point") And ((CABLE_POINT.CAPACITY)="12"));


The query runs from a form where i have a combo box to select different projects according to their ID. Sometime a project might have a record of CABLE_POINT.CABLE_POINT_TYPE ="Break Out Splice Point" And CABLE_POINT.CAPACITY="12" present. Sometimes it wont. Therefore if there is no record present i want to the query to display "0"
Go to the top of the page
 
+
kbrewster
post Oct 2 2009, 09:12 AM
Post #2

UtterAccess VIP
Posts: 4,503
From: NH



Have you tried using Nz around the Count function?

SELECT Nz(Count(CABLE_POINT.CABLE_POINT_TYPE),0) AS [No of 12f Loop Joint]...
Go to the top of the page
 
+
mark909
post Oct 2 2009, 09:19 AM
Post #3

UtterAccess Addict
Posts: 117



No joy there. I think Nz only returns a 0 if there is a null value.

The problem is that there is no record at all. What I want to be able to do is display 0 if no record is present.

Thanks for help sad.gif
Go to the top of the page
 
+
kbrewster
post Oct 2 2009, 09:21 AM
Post #4

UtterAccess VIP
Posts: 4,503
From: NH



When you say "query runs from a form..." do you mean you are opening the query from the form? Or are you showing the results in a control on the form?
Go to the top of the page
 
+
mark909
post Oct 2 2009, 09:30 AM
Post #5

UtterAccess Addict
Posts: 117



I have a combo box on a form that is used to select a project according to the Project_ID

I then have a button that runs the query.

This gives individual counts of items according to the project.

So sometimes this item might be present in a project in which case the query gives that result.

Other times this specific item isnt present in a project so the query result will be blank. What i want is where the result would be blank is for the query to display 0 instead.

I hope that makes sense! sad.gif
Go to the top of the page
 
+
kbrewster
post Oct 2 2009, 09:56 AM
Post #6

UtterAccess VIP
Posts: 4,503
From: NH



So you are actually opening the query for the user to just see the count of records? Why not display this on the form? You could place an unbound control on the form and set the control source to...

=Nz(DCount("*", "NameOfQuery"), 0)
Go to the top of the page
 
+
vtd
post Oct 2 2009, 11:01 PM
Post #7

Retired Moderator
Posts: 19,667



Kristen

DCount() always returns a numeric count, including zero and hence, there is no need for Nz() in the expression.
Go to the top of the page
 
+
vtd
post Oct 2 2009, 11:15 PM
Post #8

Retired Moderator
Posts: 19,667



This is aside from the problem of displaying 0 but you should not need the GROUP BY clause and the HAVING clause should be a WHERE clause.

Your Query/SQL restricts the returned data to 1 PROJECT_ID, 1 CABLE_POINT_TYPE and 1 CAPACITY so there is no point in asking JET/ACCDE to group the returned rows by these 3 Fields. In the GROUP BY clause is removed, then you cannot use the HAVING clause so you need to use WHERE clause. OTOH, even if the HAVING clause is possible, the WHERE clause is more efficient than the HAVING clause so use the WHERE condition whenever you can.

Check Access Help for the differences between the WHERE clause and the HAVING clause.

In fact, if I assume that you only want to display the count in a TextBox Control on the Form, then you should be able to use just the DCount() function without needing the Query / SQL. You can simply use the Table as the Domain for DCount() and use the 3rd argument of DCount for the selection criteria. Check Access Help for DCount() usage and syntax.
Go to the top of the page
 
+
Doug Steele
post Oct 3 2009, 04:13 PM
Post #9

UtterAccess VIP
Posts: 18,494
From: St. Catharines, ON (Canada)



The problem is that Count in a query cannot count rows that aren't there, so what you need to do is introduce a dummy 0 row and sum the two subselects.

SELECT Sum(Subtotal) AS [No of 12f Loop Joint]
FROM
(
SELECT Count(*) AS Subtotal
FROM CABLE_POINT
WHERE PROJECT_ID=Forms!frmReqForm!txtProject_ID
AND CABLE_POINT_TYPE="Break Out Splice Point"
AND CAPACITY)="12"
GROUP BY PROJECT_ID, CABLE_POINT_TYPE, CAPACITY
UNION
SELECT DISTINCT 0
FROM CABLE_POINT
) AS Subselects

When there are no rows that match the criteria in the first subselect, you'll still have a row returning 0 from the second subselect.
Go to the top of the page
 
+
mark909
post Oct 5 2009, 03:36 AM
Post #10

UtterAccess Addict
Posts: 117



Thanks guys im not actually trying to display the value in a form.

Its actually for a query to create a subreport in a report.

Thanks Doug that works brilliantly sad.gif
Go to the top of the page
 
+
mark909
post Oct 5 2009, 08:24 AM
Post #11

UtterAccess Addict
Posts: 117



Is it possible to perform a similar query on sum in this query?

SELECT CABLE_LINE.PROJECT_ID, CABLE_LINE.CABLE_LINE_TYPE, CABLE_LINE.FIBRE_COUNT, Sum(CABLE_LINE.LINE_LENGTH) AS SumOfLINE_LENGTH

FROM CABLE_LINE

GROUP BY CABLE_LINE.PROJECT_ID, CABLE_LINE.CABLE_LINE_TYPE, CABLE_LINE.FIBRE_COUNT
HAVING (((CABLE_LINE.PROJECT_ID)=[Forms]![frmBillOfMaterials]![txtProject_ID]) AND ((CABLE_LINE.CABLE_LINE_TYPE)="New Duct Cable") AND ((CABLE_LINE.FIBRE_COUNT)="12"));

Ive tried

SELECT Sum(Subselects.Subtotal) AS [SumOfLINE_LENGTH]
FROM (SELECT Sum(*) AS Subtotal
FROM CABLE_LINE
WHERE PROJECT_ID=Forms!frmBillOfMaterials!txtProject_ID
AND CABLE_LINE_TYPE=”New Duct Cable”
AND FIBRE_COUNT=”12”
GROUP BY PROJECT_ID, CABLE_LINE_TYPE, FIBRE_COUNT
UNION
SELECT DISTINCT 0
FROM CABLE_LINE
) AS Subselects;

But cant get it to work
Go to the top of the page
 
+
Doug Steele
post Oct 16 2009, 06:06 AM
Post #12

UtterAccess VIP
Posts: 18,494
From: St. Catharines, ON (Canada)



Sorry, for some reason I never got notified that you had a follow-up post to this thread.

First of all, you cannot use * with the Sum function: you must use the name of a numeric field. Asterisks are only used with the Count function.

Since you're only interested in known values of PROJECT_ID, CABLE_LINE_TYPE and FIBRE_COUNT, you should be able to use:

SELECT PROJECT_ID, CABLE_LINE_TYPE, FIBRE_COUNT, Sum(Subselects.Subtotal) AS [SumOfLINE_LENGTH]
FROM (SELECT PROJECT_ID, CABLE_LINE_TYPE, FIBRE_COUNT, Sum(LINE_LENGTH) AS Subtotal
FROM CABLE_LINE
WHERE PROJECT_ID=Forms!frmBillOfMaterials!txtProject_ID
AND CABLE_LINE_TYPE=”New Duct Cable”
AND FIBRE_COUNT=”12”
GROUP BY PROJECT_ID, CABLE_LINE_TYPE, FIBRE_COUNT
UNION
SELECT DISTINCT Forms!frmBillOfMaterials!txtProject_ID, "New Duct Cable", "12", 0
FROM CABLE_LINE
) AS Subselects;

or even

SELECT Forms!frmBillOfMaterials!txtProject_ID, "New Duct Cable", "12", Sum(Subselects.Subtotal) AS [SumOfLINE_LENGTH]
FROM (SELECT Sum(LINE_LENGTH) AS Subtotal
FROM CABLE_LINE
WHERE PROJECT_ID=Forms!frmBillOfMaterials!txtProject_ID
AND CABLE_LINE_TYPE=”New Duct Cable”
AND FIBRE_COUNT=”12”
UNION
SELECT DISTINCT 0
FROM CABLE_LINE
) AS Subselects;


(The problem would be harder if you wanted all values of CABLE_LINE_TYPE or FIBRE_COUNT, even if there was nothing for that specific PROJECT_ID)

(Incidentally, I hope you don't mind, but I used your original question as the basis for a column at Database Journal: Reporting what's not there)

Edited by: djsteele on Fri Oct 16 7:09:14 EDT 2009.
Go to the top of the page
 
+
mark909
post Oct 16 2009, 06:53 AM
Post #13

UtterAccess Addict
Posts: 117



Thanks doug I posted another new question regarding this and someone responded with the 2nd subselects answer.

SELECT Forms!frmBillOfMaterials!txtProject_ID, "New Duct Cable", "12", Sum(Subselects.Subtotal) AS [SumOfLINE_LENGTH]
FROM (SELECT Sum(LINE_LENGTH) AS Subtotal
FROM CABLE_LINE
WHERE PROJECT_ID=Forms!frmBillOfMaterials!txtProject_ID
AND CABLE_LINE_TYPE=”New Duct Cable”
AND FIBRE_COUNT=”12”
UNION
SELECT DISTINCT 0
FROM CABLE_LINE
) AS Subselects;

The article you wrote in the database journal looks interesting . Ill have a read when i get the chance!
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 1st August 2014 - 05:29 PM