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

 

[Bob Barrows]

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"));

Ah, I think I understand your question better this time. It could still have
been clearer if you had said something like:
"I want to retrieve the ESNs that have a single service type: "basic". So
from this set of sample data, this is only true for ESN 345. All the others
have multiple service types."

Are there any service types that begin with the letter "A", or that would be
sorted before "Basic" if you sorted the data by Service Type ascending? If
not, this could be very simple:

SELECT Count(*) AS ["Basic Activations"]
FROM (
SELECT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation"
GROUP BY ESN
HAVING MAX(SERVICE)="Basic"
) As q

If there are other service types besides the three you have mentioned, and
some would be sorted before "Basic", this gets more complex. Let's start by
creating a query to retrieve ESNs that have service types other than
"basic":
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE<>"Basic"

Then, there are several options: NOT IN, NOT EXISTS, and frustrated outer
join. Let's use NOT IN:

SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE="Basic"
AND ESN NOT IN (
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE<>"Basic")

Then use this as the datasource for the count, as above.
SELECT Count(*) AS ["Basic Activations"]
FROM (
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE="Basic"
AND ESN NOT IN (
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE<>"Basic")
) As q

 

[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"));

Ah, I think I understand your question better this time. It could still have
been clearer if you had said something like:
"I want to retrieve the ESNs that have a single service type: "basic". So
from this set of sample data, this is only true for ESN 345. All the others
have multiple service types."

Are there any service types that begin with the letter "A", or that wouldbe
sorted before "Basic" if you sorted the data by Service Type ascending? If
not, this could be very simple:

SELECT Count(*) AS ["Basic Activations"]
FROM (
SELECT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation"
GROUP BY ESN
HAVING MAX(SERVICE)="Basic"
) As q

If there are other service types besides the three you have mentioned, and
some would be sorted before "Basic", this gets more complex. Let's start by
creating a query to retrieve ESNs that have service types other than
"basic":
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE<>"Basic"

Then, there are several options: NOT IN, NOT EXISTS, and frustrated outer
join. Let's use NOT IN:

SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE="Basic"
AND ESN NOT IN (
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE<>"Basic")

Then use this as the datasource for the count, as above.
SELECT Count(*) AS ["Basic Activations"]
FROM (
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE="Basic"
AND ESN NOT IN (
SELECT DISTINCT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation" AND SERVICE<>"Basic")
) As q- Hide quoted text -

- Show quoted text -

Hey Bob,

Thank you so much for your help!

The query works for counting services only equal to only 'Basic', I am
delighted!!

Service will always be equal to a minimum of basic so the options are:

Basic
Basic & Tracking
Basic & Data Push
Basic & 100MB
Basic & 500MB
Basic & Tracking & Data Push
Basic & Tracking & 100MB
Basic & Tracking & 500MB

There are other services but this was the part of the code I was
having most difficulty with.

How do I code only equal to Basic & Tracking?

I tried adding the word Tracking but it isn't working...

SELECT Count(*) AS ["Basic Activations"]

FROM (
SELECT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation"
GROUP BY ESN
HAVING MAX(SERVICE)=("Basic" AND "Tracking")
) As q

Best regards,
Aine

 

[Bob Barrows]

Service will always be equal to a minimum of basic so the options are:

Basic
Basic & Tracking
Basic & Data Push
Basic & 100MB
Basic & 500MB
Basic & Tracking & Data Push
Basic & Tracking & 100MB
Basic & Tracking & 500MB

It looks as if "Basic" is always present if one of the other services are
present, is that the case?

There are other services but this was the part of the code I was
having most difficulty with.

How do I code only equal to Basic & Tracking?

I tried adding the word Tracking but it isn't working...

SELECT Count(*) AS ["Basic Activations"]

FROM (
SELECT ESN FROM Data
WHERE SUBSCR_TYPE="Service Activation"
GROUP BY ESN
HAVING MAX(SERVICE)=( "Basic" AND "Tracking" )

This cannot work: MAX(SERVICE) will never be BOTH "Basic" AND "Tracking" at
the same time. It can be one or the other, but not both.

In addition, even if it is true that Basic will always be present if
Tracking (or any of the other service types) is present, you cannot use this
MAX approach - in Text columns, numbers sort before alpha characters in Jet.
So I'm surprised that you are getting the right answer with my suggested
query. If ESNs with either 100MB or 500MB were present, they would have been
counted among the results of that first suggestion I offered. Given that you
are sure you got the correct results from your data, I can only conclude
that there were no ESNs with both "Basic" and either 100MB or 500MB in your
data when you ran the query.

There are two approaches to this problem:
A. Use the second suggestion I offered. Generically:
1. create a query that returns the distinct ESNs that don't meet your
desired criteria
2. in a second query, use NOT IN, or NOT EXISTS, or a frustrated outer
join to return the distinct ESNs from Data that aren't in the resultset from
the first query.
3.then count the records returned by the second query.

B. This is a little slicker, in that it's data-driven, but it requires you
to become comfortable with bitwise comparisons. It wil only work if there
are fewer than 32 possible service types. Basically, it involves assigning
codes that correspond to the digit values in a binary number. From the
right, the digits represent 1,2,4,8,16,... Notice how the value of each
digit is obtained by doubling the previous digit.
When a digit is 0, it is thought of as "off". When 1, it is "on". Looking at
the 5 rightmost columns, this is how it plays out when only a single digit
is "on"
decimal binary comment
0 ... 00000 no bits are "on"
1 ... 00001 the 1-bit is "on"
2 ... 00010 the 2-bit is "on"
4 ... 00100 the 4-bit is "on"
8 ... 01000 the 8-bit is "on"
16 ... 10000 the 16-bit is "on"


So, if we assign 0 to Basic, 1 to Tracking, and 2 to Data Push, here is how
it plays out with your original sample data:
ESN |Subscriber Type|Service Type | ServiceBit

123 | Activation | Basic | 0
123 | Activation | Tracking | 1
123 | Activation | Data Push | 2
Sum 3

234 | Renewal | Basic | 0
234 | Renewal | Tracking | 1
234 | Renewal | Data Push | 2
Sum 3

345 | Activation | Basic | 0
Sum 0

567 | Activation | Basic | 0
567 | Activation | Tracking | 1
Sum 1

678 | Renewal | Basic | 0
678 | Renewal | Data Push | 2
Sum 2

789 | Activation | Basic | 0
789 | Activation | Tracking | 1
Sum 1

I've added the summary rows to illustrate what I am planning. You determine
the combination of all service types for an ESN by summing the bit values
assigned to each. As you can see, when the sum is 0, only Basic exists. When
1, Basic and Tracking exist. When 2, Basic and Data Push exist. When 3, all
three exist. I will leave it as an exercise for you to work out what happens
with 100MB and 500MB

So, the steps you need to perform are:
1. Create a ServiceTypes table with two columns: SERVICE (Text) and
ServiceBit (Number - Long)
a. The SERVICE column should be the primary key
2. Enter the service types and bit values per above:
Basic 0
Tracking 1
Data Push 2
etc.
3. Create a saved query that joins Data to ServiceTypes, groups by ESN
and sums the bit values for each ESN. You might need three of these, one for
Renewals, one for Activations, and another for both. Here is the sql for the
Activation results query (qServiceBitSumActivation):
SELECT ESN, SUM(ServiceBit) As ServiceBitSum
FROM Data As d JOIN ServiceTypes As t
ON d.SERVICE = t.SERVICE
WHERE SUBSCR_TYPE="Service Activation"
GROUP BY ESN
4 Use the saved query as the source for a query that counts the results:
Basic Only:
SELECT Count(*) As BasicCount
FROM qServiceBitSumActivation
WHERE ServiceBitSum = 0

Basic and Tracking:
SELECT Count(*) As BasicAndTrackingCount
FROM qServiceBitSumActivation
WHERE ServiceBitSum = 1

Do you see why I said this was slicker? Evey question you want to ask is
answered by a variation of this simple query. This is the solution I have
used in similar situations and I can attest to its effectiveness.

HTH,
Bob Barrows

 

[Bob Barrows]

When a digit is 0, it is thought of as "off". When 1, it is "on".
Looking at the 5 rightmost columns, this is how it plays out when
only a single digit is "on"
decimal binary comment
0 ... 00000 no bits are "on"
1 ... 00001 the 1-bit is "on"
2 ... 00010 the 2-bit is "on"
4 ... 00100 the 4-bit is "on"
8 ... 01000 the 8-bit is "on"
16 ... 10000 the 16-bit is "on"

I failed to explain why we can sum the bit values to find which ones are on.
Let me try to rectify that here:

The above chart shows the decimal values when only a single bit is "on".
Multiple bits can be "on". here is the binary number where all bits are
"on":
11111
This is the equivalent of 31 in decimal.
Here are a couple more examples:
if the 1 and 8 bits are "on" the binary number is 01001, which is the
equivalent of 9 in decimal.
if the 2, 4 and 16 bits are "on", the binary number is 10110, which is the
equivalent of 24 in decimal.

Note that these decimal equivalents (31, 9 and 24) are found by summing the
values of the bits that are "on".

This can be used to model situations (such as yours) where multiple,
distinct options can be turned on at once. That "distinct" word is critical:
my suggested solution requires modification if 2 records for an ESN can
exist with the same service type. In that situation, you will need a query
to return distinct service types for each ESN. I don't think that's the case
in your application so I won't dwell on this.

 

[ainese]

I failed to explain why we can sum the bit values to find which ones are on.
Let me try to rectify that here:

The above chart shows the decimal values when only a single bit is "on".
Multiple bits can be "on". here is the binary number where all bits are
"on":
11111
This is the equivalent of 31 in decimal.
Here are a couple more examples:
if the 1 and 8 bits are "on" the binary number is 01001, which is the
equivalent of 9 in decimal.
if the 2, 4 and 16 bits are "on", the binary number is 10110, which is the
equivalent of 24 in decimal.

Note that these decimal equivalents (31, 9 and 24) are found by summing the
values of the bits that are "on".

This can be used to model situations (such as yours) where multiple,
distinct options can be turned on at once. That "distinct" word is critical:
my suggested solution requires modification if 2 records for an ESN can
exist with the same service type. In that situation, you will need a query
to return distinct service types for each ESN. I don't think that's the case
in your application so I won't dwell on this.

Bob,
You are a legend! Thank you for this All is working perfectly!
wohoo!!
Kindest regards,
Aine