I am running CR 8.5 on a SQL 2000 DB
Sample table & fields
Table1.AcctNo
Table2.AcctNo
Table2.PriceID
There are multiple records in Table2 for each AcctNo but the count of records for each AcctNo will vary.
AcctNo PriceID
1 5
1 6
1 7
1 8
2 5
2 6
2 7
2 8
3 5
3 6
3 7
3 9
4 9
5 9
5 6
I am trying to create a formula that will name each “identical” combination of PriceID’s.
For example: if the PriceID’s for a single account are 5,6,7 & 8 then “STANDARD”
if the PriceID’s for a single account are 5,6,7 & 9 then “OTHER”
if the PriceID’s for a single account are 9 then “OTHER2”
if the PriceID’s for a single account are 6 & 9 then “OTHER3”
etc.
I have tried using if/then formulas like:
If {Table2.PriceID = 5 and {Table2.PriceID = 6 and {Table2.PriceID = 7 and {Table2.PriceID = 8 then “STANDARD”
Else if {Table2.PriceID = 5 and {Table2.PriceID = 6 and {Table2.PriceID = 7 and {Table2.PriceID = 9 then “OTHER” ……
But the results are not accurate.
Can anyone help me with this?
Sample table & fields
Table1.AcctNo
Table2.AcctNo
Table2.PriceID
There are multiple records in Table2 for each AcctNo but the count of records for each AcctNo will vary.
AcctNo PriceID
1 5
1 6
1 7
1 8
2 5
2 6
2 7
2 8
3 5
3 6
3 7
3 9
4 9
5 9
5 6
I am trying to create a formula that will name each “identical” combination of PriceID’s.
For example: if the PriceID’s for a single account are 5,6,7 & 8 then “STANDARD”
if the PriceID’s for a single account are 5,6,7 & 9 then “OTHER”
if the PriceID’s for a single account are 9 then “OTHER2”
if the PriceID’s for a single account are 6 & 9 then “OTHER3”
etc.
I have tried using if/then formulas like:
If {Table2.PriceID = 5 and {Table2.PriceID = 6 and {Table2.PriceID = 7 and {Table2.PriceID = 8 then “STANDARD”
Else if {Table2.PriceID = 5 and {Table2.PriceID = 6 and {Table2.PriceID = 7 and {Table2.PriceID = 9 then “OTHER” ……
But the results are not accurate.
Can anyone help me with this?