HELP PLEASE

[ainese]

ESN | Subscriber Type | Service Type

123 | Activation | Basic
123 | Activation | Tracking
123 | Activation | Data Push


234 | Renewal | Basic
234 | Renewal | Tracking
234 | Renewal | Data Push


345 | Activation | Basic


567 | Activation | Basic
567 | Activation | Tracking


678 | Renewal | Basic
678 | Renewal | Data Push


789 | Activation | Basic
789 | Activation | Tracking


This is an example of my data that I have imported from Excel. What I
am having alot of difficulty with is that I'm trying to write a query
that counts ESN where Service Type is only = 'Basic' and nothing
else.
So from the above data I should get 1. But I am getting 2 with my
coding. Please see below


Can anyone advise how to code this query in MS Access please?


This is what I've got to date but it is not working out correctly...


SELECT DISTINCT (Count(F.ESN)) AS ["Basic Activations"]
FROM Data
AS F INNER JOIN Data AS G ON F.ESN = G.ESN
WHERE (((G.ESN) Not In (SELECT ESN FROM Data WHERE SERVICE =
'Tracking')) AND ((F.SERVICE)="Basic") AND ((G.SERVICE)<>"Data Push")
AND ((G.SUBSCR_TYPE)="Service Activation"));


PLEASE HELP me as I am pulling my hair out at this stage.....


Thank you in advance,
Aine

 

[Bjørn C. Winkel]

"ainese" skrev i meddelelsen

ESN | Subscriber Type | Service Type

123 | Activation | Basic
123 | Activation | Tracking
123 | Activation | Data Push


234 | Renewal | Basic
234 | Renewal | Tracking
234 | Renewal | Data Push


345 | Activation | Basic


567 | Activation | Basic
567 | Activation | Tracking


678 | Renewal | Basic
678 | Renewal | Data Push


789 | Activation | Basic
789 | Activation | Tracking


This is an example of my data that I have imported from Excel. What I
am having alot of difficulty with is that I'm trying to write a query
that counts ESN where Service Type is only = 'Basic' and nothing
else.
So from the above data I should get 1. But I am getting 2 with my
coding. Please see below


Can anyone advise how to code this query in MS Access please?


This is what I've got to date but it is not working out correctly...


SELECT DISTINCT (Count(F.ESN)) AS ["Basic Activations"]
FROM Data
AS F INNER JOIN Data AS G ON F.ESN = G.ESN
WHERE (((G.ESN) Not In (SELECT ESN FROM Data WHERE SERVICE =
'Tracking')) AND ((F.SERVICE)="Basic") AND ((G.SERVICE)<>"Data Push")
AND ((G.SUBSCR_TYPE)="Service Activation"));


PLEASE HELP me as I am pulling my hair out at this stage.....


Thank you in advance,
Aine

Hi Aine

SQL is not my strongest language, but perhaps a 'Group buy' [Service type]
would help?

B.R. Bjørn C. Winkel

 

[XPS350]

Try:
SELECT Count(ESN) AS NumberOfESN
FROM YourTable
WHERE ESN Not In (SELECT DISTINCT ESN FROM YourTable WHERE [Service Type]<>"Basic") AND [Service Type]="Basic"

Peter
http://access.xps350.com/

 

[Bob Quintal]

m:

ESN | Subscriber Type | Service Type

123 | Activation | Basic
123 | Activation | Tracking
123 | Activation | Data Push


234 | Renewal | Basic
234 | Renewal | Tracking
234 | Renewal | Data Push


345 | Activation | Basic


567 | Activation | Basic
567 | Activation | Tracking


678 | Renewal | Basic
678 | Renewal | Data Push


789 | Activation | Basic
789 | Activation | Tracking


This is an example of my data that I have imported from Excel.
What I am having alot of difficulty with is that I'm trying to
write a query that counts ESN where Service Type is only = 'Basic'
and nothing else.
So from the above data I should get 1. But I am getting 2 with my
coding. Please see below


Can anyone advise how to code this query in MS Access please?


This is what I've got to date but it is not working out
correctly...


SELECT DISTINCT (Count(F.ESN)) AS ["Basic Activations"]
FROM Data
AS F INNER JOIN Data AS G ON F.ESN = G.ESN
WHERE (((G.ESN) Not In (SELECT ESN FROM Data WHERE SERVICE =
'Tracking')) AND ((F.SERVICE)="Basic") AND ((G.SERVICE)<>"Data
Push") AND ((G.SUBSCR_TYPE)="Service Activation"));


PLEASE HELP me as I am pulling my hair out at this stage.....


Thank you in advance,
Aine

An inner join will never return the record where there is only the
one [service type]

You'll ned to have one query call a second query

The second query is
SELECT G ESN from data as G where G.SERVICE <> "Basic"

The first query is
SELECT Count(F.ESN) as from DATA as F
LEFT JOIN on F.ESN = G.ESN
WHERE G.ESN is null and F.SUBSCR_TYPE="Service Activation"