Count Query not showing all records

Q

QM

The query below is suppose to present all the vendor records where [New
Business Hold] = closed or open. The problem is, it does just that and only
that.

Is there a way to have the query present all vendors even if [New Business
Hold] = open or closed is zero, present a zero? When presenting this and
other data in a report, the subreport disappears if the specific vendor does
not have an "open" or "closed" in this field. When the vendor does meet this
criteria, the report looks presentable, but without it looks like something
is missing...

Here is my SQL data;

SELECT Vendors.supplier_name, Count([Concern Log].AssetID) AS
CountOfAssetID, [Concern Log].[SNew Business Hold], [New Business
Hold].NB_Status, Vendors.ID
FROM [New Business Hold] INNER JOIN (Vendors INNER JOIN [Concern Log] ON
Vendors.ID = [Concern Log].Supplier) ON [New Business Hold].NBID = [Concern
Log].[SNew Business Hold]
WHERE ((([Concern Log].Date) Between
DateSerial(Year(Date()),Month(Date()),Day(Date())-(Day(Date()+1))) And
DateSerial(Year(Date()),Month(Date())-12,Day(Date()-(Day(Date()-1))))))
GROUP BY Vendors.supplier_name, [Concern Log].[SNew Business Hold], [New
Business Hold].NB_Status, Vendors.ID
HAVING ((([New Business Hold].NB_Status)="open" Or ([New Business
Hold].NB_Status)="closed"))
ORDER BY Vendors.supplier_name;

Thanks in advance.
 
M

MGFoster

QM said:
The query below is suppose to present all the vendor records where [New
Business Hold] = closed or open. The problem is, it does just that and only
that.

Is there a way to have the query present all vendors even if [New Business
Hold] = open or closed is zero, present a zero? When presenting this and
other data in a report, the subreport disappears if the specific vendor does
not have an "open" or "closed" in this field. When the vendor does meet this
criteria, the report looks presentable, but without it looks like something
is missing...

Here is my SQL data;

SELECT Vendors.supplier_name, Count([Concern Log].AssetID) AS
CountOfAssetID, [Concern Log].[SNew Business Hold], [New Business
Hold].NB_Status, Vendors.ID
FROM [New Business Hold] INNER JOIN (Vendors INNER JOIN [Concern Log] ON
Vendors.ID = [Concern Log].Supplier) ON [New Business Hold].NBID = [Concern
Log].[SNew Business Hold]
WHERE ((([Concern Log].Date) Between
DateSerial(Year(Date()),Month(Date()),Day(Date())-(Day(Date()+1))) And
DateSerial(Year(Date()),Month(Date())-12,Day(Date()-(Day(Date()-1))))))
GROUP BY Vendors.supplier_name, [Concern Log].[SNew Business Hold], [New
Business Hold].NB_Status, Vendors.ID
HAVING ((([New Business Hold].NB_Status)="open" Or ([New Business
Hold].NB_Status)="closed"))
ORDER BY Vendors.supplier_name;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Query probably should be like this:

SELECT V.supplier_name, Count(CL.AssetID) AS CountOfAssetID,
CL.[SNew Business Hold], Nz(NBH.NB_Status,"NULL") As Status, V.ID

FROM [New Business Hold] As NBH RIGHT JOIN (Vendors As V INNER JOIN
[Concern Log] As CL ON V.ID = CL.Supplier) ON NBH.NBID = CL.[SNew
Business Hold]

WHERE CL.Date Between DateSerial(Year(Date()),Month(Date()),Day(Date())
(Day(Date()+1))) And
DateSerial(Year(Date()),Month(Date())-12,Day(Date()-(Day(Date()-1)))
AND NBH.NB_Status in ("open","closed")

GROUP BY V.supplier_name, CL.[SNew Business Hold], NBH.NB_Status, V.ID

ORDER BY V.supplier_name;

You needed an OUTER JOIN (RIGHT in this case) and the HAVING clause
should have been included in the WHERE clause.

I put an Nz() function around the NBH.NB_Status w/ "NULL" as the
substitute when that value is NULL. You can change that value to
whatever you want.

Read about RIGHT/LEFT JOINs in the Access help to understand them
better.

In a report, if you are using a subreport that sometimes has data and
other times doesn't have data, you can use the subreport's HasData
property in one of the report's sections OnFormat event to hide or show
a label that indicates if there is No data for that particular
record(s). E.g.:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!lblAgreements.Visible = Not Me!srptPendingCAs.Report.HasData
End Sub

In the example, the Label lblAgreements will be set to Visible when the
subreport "srptPendingCAs" has data; otherwise, it will be invisible.
The Caption of the label is "None" which indicates that there were no
records for the Pending CAs subreport. I'd set the Height of the
subreport to 0.0417" and the CanShrink & CanGrow properties to Yes.
That will keep the format of the report from looking funky when there
isn't any data in the subreport.

The set up on the report was 2 labels like this:

Pending CAs: None <- this label is set to visible/invisible
[ sub report data ] <- the subreport will contract/expand 'cuz of
CanShrink/CanGrow

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyi6sYechKqOuFEgEQJjrwCfe3/qGlC4dZzbeEgbVJZK8gcFP3QAn2nX
jG9ob1aCd3vdD1y4j1Kvqx5h
=eCiu
-----END PGP SIGNATURE-----
 
Top