If then statement

C

chuckw

Hi,

I'm not very familiar with IF Then Statements but I know
I need one.

We want to send out coupons for $10 off a treatment. We
have four types of treatments: Massage, Facials, BTA and
BEAs. There are more than one kind of Massages, Facials
etc. which are represented by codes. There is FAC1,
FAC2, MAS1, MAS2, MAS3, BEA1, BEA2, BTA1, BTA2.

What we are trying to do is to find everyone who had a
massage but not a facial. This would be everyone who has
had MAS1, MAS2 or MAS3 but not FAC1 or FAC2. We would
then send them a Letter with a coupon for a facial. We
also only want to send this once. So if they only get
the letter with the coupon one time.

To make things a little more complicated, we also want to
find everyone who has had a facial but not a massage (or
FAC1, FAC2 but not MAS1, MAS2 or MAS3) and send them a
coupon for a facial. I probably have to do this in two
different queries but it would be nice to place this
under one button.

Can anyone help with the SQL?

Thanks,

Chuck
 
B

Brian Camire

Assuming you have a table that looks something like this:

Treatment Date, Treatment Code, Client ID
11/03/2003, FAC2, 1
11/03/2003, FAC2, 2
11/03/2003, MAS2, 3
11/10/2003, MAS1, 1
11/10/2003, BEA1, 1
11/10/2003, FAC2, 2
11/10/2003, MAS3, 3
..
..
..

You might try something like this:

1. Create a query (say, named "Query1") that returns the clients who have
had massages:

SELECT DISTINCT
[Your Table].[Client ID]
FROM
[Your Table]
WHERE
[Your Table].[Treatment Code] LIKE "MAS*"

2. Create a query (say, named "Query2") that returns the clients who have
had facials:

SELECT DISTINCT
[Your Table].[Client ID]
FROM
[Your Table]
WHERE
[Your Table].[Treatment Code] LIKE "FAC*"

3. Create a query that returns the clients in Query1 but not Query2 (that
is, the clients who have had massages but not facials):

SELECT
[Query1].[Client ID]
FROM
[Query1]
LEFT JOIN
[Query2]
ON
[Query1].[Client ID] = [Query2].[Client ID]
WHERE
[Query2].[Client ID] IS NULL

4. Create a query that returns the clients in Query2 but not Query1 (that
is, the clients who have had facials but not massages):

SELECT
[Query2].[Client ID]
FROM
[Query2]
LEFT JOIN
[Query1]
ON
[Query2].[Client ID] = [Query1].[Client ID]
WHERE
[Query1].[Client ID] IS NULL
 
K

Kelvin

You could create a crosstab that looks something like

ClientID FAC MAS BTA BEA
1 2 3 1
2 1
3 2 1

The numbers under each category are the number of times they have had a
treatment.

Then you could create simpler queries where you check the counts to see if
they've had that type of treatment. This would be good to use to determine
good repeat customers too.

SendCoupon:iif(([Fac]>0 and isnull([Mas])) or (isnull([fac]) and
[Mas]>1),True,False)

To keep track if the've been sent a coupon you might want to add another
field to the customer table that gets updated after you print the coupons.
Or another table to list all the coupons that were sent and to whom, this
way if you have another coupon to give out, it won't think that the person
already got one.

Kelvin
 
C

ChuckW

Kelvin,

Thanks for your help with my question from last week. I
created a cross table query like you suggested. I then
created another query using the "SendCoupon:iif..."
statement. The full code follows:

SELECT TimeMatters2.EVE_Client, TimeMatters2.[Bio-
Energetic Assessment], TimeMatters2.[BioTerrain
Assessment], TimeMatters2.[Chiropractic Adjustment],
TimeMatters2.Facial, TimeMatters2.Massage, TimeMatters2.
[Reflexology Sessions], IIf(([Massage]>0 And IsNull
([Massage])) Or (IsNull([Facial]) And [Massage]
0),True,False) AS SendCoupon
Click to expand...
FROM TimeMatters2;


TimeMatters2 is my cross tab query. It assigns a -1 to
everyone who has had a Massage but not a Facial.
However, it does not do the same for everyone who has had
a facial and not a massage. It is assigning 0 to these
people. Any thoughts on how to change this?

Thanks,

Chuck
-----Original Message-----
You could create a crosstab that looks something like

ClientID FAC MAS BTA BEA
1 2 3 1
2 1
3 2 1

The numbers under each category are the number of times they have had a
treatment.

Then you could create simpler queries where you check the counts to see if
they've had that type of treatment. This would be good to use to determine
good repeat customers too.

SendCoupon:iif(([Fac]>0 and isnull([Mas])) or (isnull ([fac]) and
[Mas]>1),True,False)

To keep track if the've been sent a coupon you might want to add another
field to the customer table that gets updated after you print the coupons.
Or another table to list all the coupons that were sent and to whom, this
way if you have another coupon to give out, it won't think that the person
already got one.

Kelvin

Hi,

I'm not very familiar with IF Then Statements but I know
I need one.

We want to send out coupons for $10 off a treatment. We
have four types of treatments: Massage, Facials, BTA and
BEAs. There are more than one kind of Massages, Facials
etc. which are represented by codes. There is FAC1,
FAC2, MAS1, MAS2, MAS3, BEA1, BEA2, BTA1, BTA2.

What we are trying to do is to find everyone who had a
massage but not a facial. This would be everyone who has
had MAS1, MAS2 or MAS3 but not FAC1 or FAC2. We would
then send them a Letter with a coupon for a facial. We
also only want to send this once. So if they only get
the letter with the coupon one time.

To make things a little more complicated, we also want to
find everyone who has had a facial but not a massage (or
FAC1, FAC2 but not MAS1, MAS2 or MAS3) and send them a
coupon for a facial. I probably have to do this in two
different queries but it would be nice to place this
under one button.

Can anyone help with the SQL?

Thanks,

Chuck
Click to expand...


.
Click to expand...
 
K

Kelvin

You first criteria in your IIF statement is

[Massage]>0 And IsNull([Massage])

I think the first one should have been Facial.

Kelvin

ChuckW said:
Kelvin,

Thanks for your help with my question from last week. I
created a cross table query like you suggested. I then
created another query using the "SendCoupon:iif..."
statement. The full code follows:

SELECT TimeMatters2.EVE_Client, TimeMatters2.[Bio-
Energetic Assessment], TimeMatters2.[BioTerrain
Assessment], TimeMatters2.[Chiropractic Adjustment],
TimeMatters2.Facial, TimeMatters2.Massage, TimeMatters2.
[Reflexology Sessions], IIf(([Massage]>0 And IsNull
([Massage])) Or (IsNull([Facial]) And [Massage]
0),True,False) AS SendCoupon
Click to expand...
FROM TimeMatters2;


TimeMatters2 is my cross tab query. It assigns a -1 to
everyone who has had a Massage but not a Facial.
However, it does not do the same for everyone who has had
a facial and not a massage. It is assigning 0 to these
people. Any thoughts on how to change this?

Thanks,

Chuck
-----Original Message-----
You could create a crosstab that looks something like

ClientID FAC MAS BTA BEA
1 2 3 1
2 1
3 2 1

The numbers under each category are the number of times they have had a
treatment.

Then you could create simpler queries where you check the counts to see if
they've had that type of treatment. This would be good to use to determine
good repeat customers too.

SendCoupon:iif(([Fac]>0 and isnull([Mas])) or (isnull ([fac]) and
[Mas]>1),True,False)

To keep track if the've been sent a coupon you might want to add another
field to the customer table that gets updated after you print the coupons.
Or another table to list all the coupons that were sent and to whom, this
way if you have another coupon to give out, it won't think that the person
already got one.

Kelvin

Hi,

I'm not very familiar with IF Then Statements but I know
I need one.

We want to send out coupons for $10 off a treatment. We
have four types of treatments: Massage, Facials, BTA and
BEAs. There are more than one kind of Massages, Facials
etc. which are represented by codes. There is FAC1,
FAC2, MAS1, MAS2, MAS3, BEA1, BEA2, BTA1, BTA2.

What we are trying to do is to find everyone who had a
massage but not a facial. This would be everyone who has
had MAS1, MAS2 or MAS3 but not FAC1 or FAC2. We would
then send them a Letter with a coupon for a facial. We
also only want to send this once. So if they only get
the letter with the coupon one time.

To make things a little more complicated, we also want to
find everyone who has had a facial but not a massage (or
FAC1, FAC2 but not MAS1, MAS2 or MAS3) and send them a
coupon for a facial. I probably have to do this in two
different queries but it would be nice to place this
under one button.

Can anyone help with the SQL?

Thanks,

Chuck
Click to expand...


.
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

If then help 3
Filtering out duplicates 1
Assigning different values in If Then Statement 2
If... Then 3
several questions about Outlook and Beta and then some.... 9
VBA - Row Deletion using If/Then Statement 3
Pricing Program and If-Then Statements 2
Outlook Object Model - Mail Merge 1

Top