Hi:

Can you please tell me how do I do the following:

I have a text field [dealno]; the maximum lenght for it is 9. 
ie:DEALNO	Pair	Matured	Leg 1
FX05875	P	Yes	
FX05876	P	Yes	
FX05877A		Yes	
FX05878B		Yes	
FX05879	P	Yes	
FX05879X	P	Yes	L 1

The ones that are the same until X are 'pairs' and have P in Pair field; How 
do I get not P for the others??
On the same note how do put L 2 in Leg field for the on with no X??

Below is my criteria that does not work 100%:

Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In 
("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))

/****

Leg 1: IIf(Right([dealno],1)="X","L 1")

THANKS A LOT!!!

Dan

Somehow I don't see that your sample data matches the criteria you have 
posted.

How are the first two items in your list the same?  FX05875 and  FX05876 
match up to the last character.

Would you care to try to state in words what how record 1 and 2 are a pair? 
And how the last two records are a pair? And why the middle two aren't a 
pair?

Leg 1: IIf(Right([dealno],1)="X","L 1","L 2") puts L 1 where the last char 
in DealNo is an X and L 2 in all other cases.



-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"D" <D@discussions.microsoft.com> wrote in message 
news:87B789BF-F891-4A78-9CCD-0F0C396124C3@microsoft.com...
> Hi:
>
> Can you please tell me how do I do the following:
>
> I have a text field [dealno]; the maximum lenght for it is 9.
> ie:DEALNO Pair Matured Leg 1
> FX05875 P Yes
> FX05876 P Yes
> FX05877A Yes
> FX05878B Yes
> FX05879 P Yes
> FX05879X P Yes L 1
>
> The ones that are the same until X are 'pairs' and have P in Pair field; 
> How
> do I get not P for the others??
> On the same note how do put L 2 in Leg field for the on with no X??
>
> Below is my criteria that does not work 100%:
>
> Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In
> ("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))
>
> /****
>
> Leg 1: IIf(Right([dealno],1)="X","L 1")
>
> THANKS A LOT!!!
>
> Dan
> 

Hi John:

Thank you!

The records that are "pairs= because up to X are the same chars." are the 
ones that until X have the same characters: FX05879 and FX05879X - all the 
pairs are one with x and one without.
The P for the first two is there because my condition did not work; sorry!

The middle two are not pairs because do not have X and are different.

Thanks again,

Dan



"John Spencer" wrote:

> Somehow I don't see that your sample data matches the criteria you have 
> posted.
> 
> How are the first two items in your list the same?  FX05875 and  FX05876 
> match up to the last character.
> 
> Would you care to try to state in words what how record 1 and 2 are a pair? 
> And how the last two records are a pair? And why the middle two aren't a 
> pair?
> 
> Leg 1: IIf(Right([dealno],1)="X","L 1","L 2") puts L 1 where the last char 
> in DealNo is an X and L 2 in all other cases.
> 
> 
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "D" <D@discussions.microsoft.com> wrote in message 
> news:87B789BF-F891-4A78-9CCD-0F0C396124C3@microsoft.com...
> > Hi:
> >
> > Can you please tell me how do I do the following:
> >
> > I have a text field [dealno]; the maximum lenght for it is 9.
> > ie:DEALNO Pair Matured Leg 1
> > FX05875 P Yes
> > FX05876 P Yes
> > FX05877A Yes
> > FX05878B Yes
> > FX05879 P Yes
> > FX05879X P Yes L 1
> >
> > The ones that are the same until X are 'pairs' and have P in Pair field; 
> > How
> > do I get not P for the others??
> > On the same note how do put L 2 in Leg field for the on with no X??
> >
> > Below is my criteria that does not work 100%:
> >
> > Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In
> > ("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))
> >
> > /****
> >
> > Leg 1: IIf(Right([dealno],1)="X","L 1")
> >
> > THANKS A LOT!!!
> >
> > Dan
> > 
> 
> 
> 

On Wed, 23 Jan 2008 11:34:01 -0800, D <D@discussions.microsoft.com> wrote:

>Hi:
>
>Can you please tell me how do I do the following:
>
>I have a text field [dealno]; the maximum lenght for it is 9. 
>ie:DEALNO	Pair	Matured	Leg 1
>FX05875	P	Yes	
>FX05876	P	Yes	
>FX05877A		Yes	
>FX05878B		Yes	
>FX05879	P	Yes	
>FX05879X	P	Yes	L 1
>
>The ones that are the same until X are 'pairs' and have P in Pair field; How 
>do I get not P for the others??
>On the same note how do put L 2 in Leg field for the on with no X??
>
>Below is my criteria that does not work 100%:
>
>Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In 
>("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))
>
>/****
>
>Leg 1: IIf(Right([dealno],1)="X","L 1")
>
>THANKS A LOT!!!
>
>Dan

You may be able to find these pairs with a SELF JOIN query:

SELECT A.DEALNO, B.DEALNO
FROM yourtable AS A INNER JOIN yourtable AS B
ON B.DEALNO = A.DEALNO & "X";

This query will find all pairs (triples, quads, quints...) where one record
has a given dealno value and the other record has that same value with an X
appended to it.

             John W. Vinson [MVP]

FX05875 P Yes
FX05876 P Yes
FX05877A Yes
FX05878B Yes
FX05879 P Yes
FX05879X P Yes L 1

So with that logiic, you have one pair in the above
FX05879 AND FX05879X
And
FX05875 and FX05876  are not a pair - even though your sample has a P in 
what looks like the Pair column.

I think that John Vinson has posted the beginnings of a solution.  Did you 
understand what he has posted and can you use that to get the deals marked 
with pairs?

SELECT A.DEALNO, B.DEALNO
FROM yourtable AS A INNER JOIN yourtable AS B
ON B.DEALNO = A.DEALNO & "X";
-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"D" <D@discussions.microsoft.com> wrote in message 
news:21A622A0-67F5-4071-A0DD-01A342386487@microsoft.com... 

Thanks a lot both of you!

It is a start; I have to find out how do I incorporate all the other fields.

Thanks,

Dan

"John Spencer" wrote:

> FX05875 P Yes
> FX05876 P Yes
> FX05877A Yes
> FX05878B Yes
> FX05879 P Yes
> FX05879X P Yes L 1
> 
> So with that logiic, you have one pair in the above
> FX05879 AND FX05879X
> And
> FX05875 and FX05876  are not a pair - even though your sample has a P in 
> what looks like the Pair column.
> 
> I think that John Vinson has posted the beginnings of a solution.  Did you 
> understand what he has posted and can you use that to get the deals marked 
> with pairs?
> 
> SELECT A.DEALNO, B.DEALNO
> FROM yourtable AS A INNER JOIN yourtable AS B
> ON B.DEALNO = A.DEALNO & "X";
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "D" <D@discussions.microsoft.com> wrote in message 
> news:21A622A0-67F5-4071-A0DD-01A342386487@microsoft.com... 
> 
> 
>