Assigning different values in If Then Statement

[Haji]

Hi,

I am working on a product promtion project. I want to
Identify people who have bought one product but not
another and assign them a certain value. For instance, I
have a table that includes customer name as a row and
products as columns. The values in the fields are
quantities purchased. If they have not bought a product
the quantity value is null. We have four main products we
want to examine: ProductA, ProductB, ProductC and
ProductD. If a Customer has purchased ProductA but not
ProductB then I want to assign them a value of 1 in my
promotion field. If then have bought ProductB but not
ProductC then I want to assign them a value of 2. If
they have bought ProductC but not ProductD then I want to
again assign them a value of 1. If they have bought
ProductD but not ProductA then I want to assign them a
value of 2. All other combinations, I want to assign as
a value of 0 or -1. Can anyone tell me how to do this?

Thanks,

Haji

 

[Liz]

Try this formula in a new field. You can call it
Promotion as per the example:

Promotion: IIf([productA] Is Not Null And [productb] Is
Null,1,IIf([productb] Is Not Null And [productc] Is
Null,2,IIf([productc] Is Not Null And [productd] Is
Null,1,IIf([productd] Is Not Null And [producta] Is
Null,2,0))))

Hope this helps.

 

[Haji]

Liz,

Thanks for your help. I must be doing something wrong
with my code. I keep getting an error message that
says "Wrong Number of Arguments used with Function in
Query Expression 'IIf ([Facial]......"

Here is my SQL code. Can you tell me what I am doing
wrong?

Thanks,

Haji

SELECT TimeMatters2.EVE_Client, TimeMatters2.[Bio-
Energetic Assessment], TimeMatters2.[BioTerrain
Assessment], TimeMatters2.[Chiropractic Adjustment],
TimeMatters2.Facial, TimeMatters2.Massage, TimeMatters2.
[Reflexology Session], IIf([Facial]>0 And IsNull
([Massage]),1, IIf (IsNull([Facial]) And [Massage]>0),2,
IIf (IsNull([Massage]) And [BioTerrain Assessment]>0),2,
IIf (IsNull([Bio-Energetic Assessment]) And [Facial]

0),1,) as SendCoupon

FROM TimeMatters2;

-----Original Message-----
Try this formula in a new field. You can call it
Promotion as per the example:

Promotion: IIf([productA] Is Not Null And [productb] Is
Null,1,IIf([productb] Is Not Null And [productc] Is
Null,2,IIf([productc] Is Not Null And [productd] Is
Null,1,IIf([productd] Is Not Null And [producta] Is
Null,2,0))))

Hope this helps.

-----Original Message-----
Hi,

I am working on a product promtion project. I want to
Identify people who have bought one product but not
another and assign them a certain value. For instance, I
have a table that includes customer name as a row and
products as columns. The values in the fields are
quantities purchased. If they have not bought a product
the quantity value is null. We have four main products we
want to examine: ProductA, ProductB, ProductC and
ProductD. If a Customer has purchased ProductA but not
ProductB then I want to assign them a value of 1 in my
promotion field. If then have bought ProductB but not
ProductC then I want to assign them a value of 2. If
they have bought ProductC but not ProductD then I want to
again assign them a value of 1. If they have bought
ProductD but not ProductA then I want to assign them a
value of 2. All other combinations, I want to assign as
a value of 0 or -1. Can anyone tell me how to do this?

Thanks,

Haji
.

.