problems with a summary query

  • Thread starter Thread starter Dos Equis
  • Start date Start date
D

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
 
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.
 
John,

Made both changes, works perfectly. Thank you.

Byron


John said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Am I forcing the 0's correctly? 5
#ERROR and currency issues 1
Query problems (User error) 5
Problems with a "Not" query 2

Back
Top