problems with a summary query

[Dos Equis]

Hi all,

Having a bit of a problem with a summary query. When it's just the
carrier and area, I get returns with this code:

SELECT [CNameFirst] & " " & [CNameLast] AS Carrier,
Count(tbl_SubscribersData.SubID) AS Subscribers
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscribersData ON tbl_Area.AreaID = tbl_SubscribersData.AreaID
GROUP BY [CNameFirst] & " " & [CNameLast]
HAVING ((([CNameFirst] & " " & [CNameLast])<>"Mail" And ([CNameFirst] &
" " & [CNameLast])<>"None"));

When I try to narrow down the results - (exclude DND's and CNX's)
I get no results. This is the code that's not working:

SELECT [CNameFirst] & " " & [CNameLast] AS Carrier,
Count(tbl_SubscribersData.SubID) AS Subscribers,
tbl_SubscribersData.SubDND, tbl_SubscribersData.SubCNX
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscribersData ON tbl_Area.AreaID = tbl_SubscribersData.AreaID
GROUP BY [CNameFirst] & " " & [CNameLast], tbl_SubscribersData.SubDND,
tbl_SubscribersData.SubCNX
HAVING ((([CNameFirst] & " " & [CNameLast])<>"Mail" And ([CNameFirst] &
" " & [CNameLast])<>"None") AND ((tbl_SubscribersData.SubDND) Is Null)
AND ((tbl_SubscribersData.SubCNX) Is Null));

I put it together in design mode in Access 2000 with no errors so I
assume it should work, however, it's not.

SubDND and SubCNX are yes/no boxes, should I be using something
different to exclude them?

Thanks for any help,

Byron

 

[John Spencer]

SubDND and SubCNX are yes/no boxes, should I be using something different to
exclude them?

Yes, since Yes/No fields (boolean) always have a value and are never null
you probably want to use = False

....
AND ((tbl_SubscribersData.SubDND)= False)
AND ((tbl_SubscribersData.SubCNX) =False));

And I would change the query a bit more. Change Group By To WHERE under
SubDND and SubCNX so that the records are filtered before the aggregation.
That is normally faster.

 

[Dos Equis]

John,

Made both changes, works perfectly. Thank you.

Byron

SubDND and SubCNX are yes/no boxes, should I be using something different to
exclude them?

Yes, since Yes/No fields (boolean) always have a value and are never null
you probably want to use = False

...
AND ((tbl_SubscribersData.SubDND)= False)
AND ((tbl_SubscribersData.SubCNX) =False));

And I would change the query a bit more. Change Group By To WHERE under
SubDND and SubCNX so that the records are filtered before the aggregation.
That is normally faster.

Hi all,

Having a bit of a problem with a summary query. When it's just the
carrier and area, I get returns with this code:

SELECT [CNameFirst] & " " & [CNameLast] AS Carrier,
Count(tbl_SubscribersData.SubID) AS Subscribers
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscribersData ON tbl_Area.AreaID = tbl_SubscribersData.AreaID
GROUP BY [CNameFirst] & " " & [CNameLast]
HAVING ((([CNameFirst] & " " & [CNameLast])<>"Mail" And ([CNameFirst] &
" " & [CNameLast])<>"None"));

When I try to narrow down the results - (exclude DND's and CNX's)
I get no results. This is the code that's not working:

SELECT [CNameFirst] & " " & [CNameLast] AS Carrier,
Count(tbl_SubscribersData.SubID) AS Subscribers,
tbl_SubscribersData.SubDND, tbl_SubscribersData.SubCNX
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscribersData ON tbl_Area.AreaID = tbl_SubscribersData.AreaID
GROUP BY [CNameFirst] & " " & [CNameLast], tbl_SubscribersData.SubDND,
tbl_SubscribersData.SubCNX
HAVING ((([CNameFirst] & " " & [CNameLast])<>"Mail" And ([CNameFirst] &
" " & [CNameLast])<>"None") AND ((tbl_SubscribersData.SubDND) Is Null)
AND ((tbl_SubscribersData.SubCNX) Is Null));

I put it together in design mode in Access 2000 with no errors so I
assume it should work, however, it's not.

SubDND and SubCNX are yes/no boxes, should I be using something
different to exclude them?

Thanks for any help,

Byron