problems with a summary query

From: "Dos Equis" <n.byron.griffin@xxxxxxxxxxx>
Date: 27 Nov 2006 11:55:15 -0800

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

.

Prev by Date: advanced top 100 list query
Next by Date: Occurances happening at the same time query
Previous by thread: advanced top 100 list query
Next by thread: Re: Excluding duplicate records in query
Index(es):
- Date
- Thread

Relevant Pages

Re: Query problems (User error)
... Are the fields Name and Carrier the common link between the two datasets? ... You'd do a join between these two queries by Name/Carrier and to tbl_Carrier. ... Count(tbl_SubscribersData.SubID) AS Subscribers ... Can I compine these two queries to come up with a correct pay scale? ...
(microsoft.public.access.queries)
Re: problems with a summary query
... SubDND and SubCNX so that the records are filtered before the aggregation. ... Count(tbl_SubscribersData.SubID) AS Subscribers ... When I try to narrow down the results - (exclude DND's and CNX's) ...
(microsoft.public.access.queries)