Help

  • Thread starter Thread starter Harvey
  • Start date Start date
H

Harvey

Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either 2 or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
 
Hi Harvey,
This doesn't account for a=b, and it seems there might be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)
 
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)
-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there might be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)

-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either 2 or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.
Click to expand...
.
Click to expand...
 
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are equal
to 3 when [HomeOrAway]="Away". I am not sure about (and &
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))
-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)
-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there might be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)

-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either 2 or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi again,
Now you've got me - a good puzzle! I'd love to see the
whole setup, but lacking that try the following mod of
your iif (note Mills and District are nested in an Or and
if EITHER of those is true they are And'ed with the Away
test to give a 3 if true, else 2):

Expr1: IIf([Away Score]>[Home Score],0,IIf(([League]
="Mills" Or [League]="District") And [HomeOrAway]
="Away",3,2))

If this doesn't work, then I dare not fry my brain any
more without seeing the bigger picture, maybe a sample of
data showing all the theoretical combinations with the
scores as they should be.
Your post sounds familiar - did you try this question some
time back? Any takers then?

Keep after it - you'll get it eventually!

Best Regards, hcj

-----Original Message-----
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are equal
to 3 when [HomeOrAway]="Away". I am not sure about (and &
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))
-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)
-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there might be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)


-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either 2 or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi again. Thanks for your help. I have pasted the query
below, but It's a bit jumbled up, but, if you start at
East Lancs (which is the name of one of the leagues) and
work accoss, you might be able to figure it out. The next
line starts with Turton. I think we are getting there,
the only problem now is if you look at the first mills,
you will see that is has been allocated 3 point instead
of 2, because we are at home. All the rest are O.K.
Thanks again
Harvey
League AwayTeam Date HomeOrAaway
Home Score Away Score Expr1
East Lancs Greenhill Bolwling Club 04-Apr-04
Away 116 90 2
Turton Edgworth 05-Apr-04 Home
196 136 2
District Bar Lane "C" 06-Apr-04
Home 189 165 2
Mills Bar Lane "D" 08-Apr-04 Home
206 162 3
East Lancs Colne Cricket Club 11-Apr-04
Home 125 90 2
East Lancs East Lancs Club 18-Apr-04
Away 113 110 2
Turton Bradshaw 19-Apr-04 Home
188 153 2
District Seven Stars 20-Apr-04
Home 207 135 2
Mills Railway "B" 22-Apr-04 Home
197 129 3
District Back o`th Bank 23-Apr-04
Away 202 148 3
Turton Eagley 26-Apr-04 Away 178
156 2
District Cross Guns 27-Apr-04
Away 175 158 3
Mills Cross Guns 29-Apr-04 Away
150 174 0
East Lancs Sunnybank Social Club 02-May-04
Home 124 90 2
Turton Longsight "A" 03-May-04 Home
199 90 2
District I.G.W 04-May-04 Home
208 128 2
Turton Bar Lane "B" 10-May-04 Away
202 126 2
Mills Turton "A" 13-May-04 Away
164 166 0
East Lancs St Andrews Bowling Club 16-May-04
Away 96 114 0
Turton Turton "A" 17-May-04 Home
210 117 2
Mills Little Lever CC 20-May-04 Away
183 179 3
East Lancs Turton "A" 23-May-04
Home 126 74 2
Turton Egerton 24-May-04 Away 200
141 2
District Dobbies "A" 25-May-04
Home 176 186 0
Mills Hollywood Rd 27-May-04 Home
210 98 3
Turton Dunscar 31-May-04 Home 186
148 2

-----Original Message-----
Hi again,
Now you've got me - a good puzzle! I'd love to see the
whole setup, but lacking that try the following mod of
your iif (note Mills and District are nested in an Or and
if EITHER of those is true they are And'ed with the Away
test to give a 3 if true, else 2):

Expr1: IIf([Away Score]>[Home Score],0,IIf(([League]
="Mills" Or [League]="District") And [HomeOrAway]
="Away",3,2))

If this doesn't work, then I dare not fry my brain any
more without seeing the bigger picture, maybe a sample of
data showing all the theoretical combinations with the
scores as they should be.
Your post sounds familiar - did you try this question some
time back? Any takers then?

Keep after it - you'll get it eventually!

Best Regards, hcj

-----Original Message-----
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are equal
to 3 when [HomeOrAway]="Away". I am not sure about (and &
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))
-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)

-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there
Click to expand...
might
be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)


-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either 2
or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Closing in...

It LOOKS like the 2nd and 6th Mills points should be 2 as
well, because they were "Home" and outscored the away
teams (Railway "B" and Hollywood Rd). Yes? No? District
got 2 each time it was "Home" except for 0 when the "Away"
team (Dobbie's "A") outscored them.

If above is all true, try:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf(([League]="Mills" Or [League]="District")
And [HomeOrAway]="Away",3,2)))

If this is correct, I think it can be shortened to:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf([League]="Mills" Or [League]
="District",3,2)))

If this still is not right, do one other thing first:
double check the HomeorAway entries to MAKE SURE there are
no stray spaces, especially leading spaces. We are
testing for literal strings ("Home" and "Away"). If any of
the actual entries is " Home" or " Away" or some variation
thereof, the test will always fail.

If the data are clean and we still haven't got it, brew a
cup and post back!

Enjoying this immensely! BTW, I'm in US Central Daylight
time (GMT-5). I get the idea you're not - problem?

hcj
-----Original Message-----
Hi again. Thanks for your help. I have pasted the query
below, but It's a bit jumbled up, but, if you start at
East Lancs (which is the name of one of the leagues) and
work accoss, you might be able to figure it out. The next
line starts with Turton. I think we are getting there,
the only problem now is if you look at the first mills,
you will see that is has been allocated 3 point instead
of 2, because we are at home. All the rest are O.K.
Thanks again
Harvey
League AwayTeam Date HomeOrAaway
Home Score Away Score Expr1
East Lancs Greenhill Bolwling Club 04-Apr-04
Away 116 90 2
Turton Edgworth 05-Apr-04 Home
196 136 2
District Bar Lane "C" 06-Apr-04
Home 189 165 2
Mills Bar Lane "D" 08-Apr-04 Home
206 162 3
East Lancs Colne Cricket Club 11-Apr-04
Home 125 90 2
East Lancs East Lancs Club 18-Apr-04
Away 113 110 2
Turton Bradshaw 19-Apr-04 Home
188 153 2
District Seven Stars 20-Apr-04
Home 207 135 2
Mills Railway "B" 22-Apr-04 Home
197 129 3
District Back o`th Bank 23-Apr-04
Away 202 148 3
Turton Eagley 26-Apr-04 Away 178
156 2
District Cross Guns 27-Apr-04
Away 175 158 3
Mills Cross Guns 29-Apr-04 Away
150 174 0
East Lancs Sunnybank Social Club 02-May-04
Home 124 90 2
Turton Longsight "A" 03-May-04 Home
199 90 2
District I.G.W 04-May-04 Home
208 128 2
Turton Bar Lane "B" 10-May-04 Away
202 126 2
Mills Turton "A" 13-May-04 Away
164 166 0
East Lancs St Andrews Bowling Club 16-May-04
Away 96 114 0
Turton Turton "A" 17-May-04 Home
210 117 2
Mills Little Lever CC 20-May-04 Away
183 179 3
East Lancs Turton "A" 23-May-04
Home 126 74 2
Turton Egerton 24-May-04 Away 200
141 2
District Dobbies "A" 25-May-04
Home 176 186 0
Mills Hollywood Rd 27-May-04 Home
210 98 3
Turton Dunscar 31-May-04 Home 186
148 2

-----Original Message-----
Hi again,
Now you've got me - a good puzzle! I'd love to see the
whole setup, but lacking that try the following mod of
your iif (note Mills and District are nested in an Or and
if EITHER of those is true they are And'ed with the Away
test to give a 3 if true, else 2):

Expr1: IIf([Away Score]>[Home Score],0,IIf(([League]
="Mills" Or [League]="District") And [HomeOrAway]
="Away",3,2))

If this doesn't work, then I dare not fry my brain any
more without seeing the bigger picture, maybe a sample of
data showing all the theoretical combinations with the
scores as they should be.
Your post sounds familiar - did you try this question some
time back? Any takers then?

Keep after it - you'll get it eventually!

Best Regards, hcj

-----Original Message-----
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are equal
to 3 when [HomeOrAway]="Away". I am not sure about (and &
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))

-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)

-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there might
be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)


-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either 2
or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Brilliant! Works fine, even the shortened version. Thank
you very much. I'm from Lancashire, England and the only
problem is that I can only access the internet duing
working hours 9 till 5. BTW, the game is crown green
bowls which is only played in the north of England and is
normaly played with 10 players on each side and the games
are normaly 21 up. I don't know if you are interested,
but if you are, I will email a copy of the Bowls database
to you and maybe you could give me some feedback on any
changes that could be made.
Once again thank you very much.
Harvey
-----Original Message-----
Closing in...

It LOOKS like the 2nd and 6th Mills points should be 2 as
well, because they were "Home" and outscored the away
teams (Railway "B" and Hollywood Rd). Yes? No? District
got 2 each time it was "Home" except for 0 when the "Away"
team (Dobbie's "A") outscored them.

If above is all true, try:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf(([League]="Mills" Or [League]="District")
And [HomeOrAway]="Away",3,2)))

If this is correct, I think it can be shortened to:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf([League]="Mills" Or [League]
="District",3,2)))

If this still is not right, do one other thing first:
double check the HomeorAway entries to MAKE SURE there are
no stray spaces, especially leading spaces. We are
testing for literal strings ("Home" and "Away"). If any of
the actual entries is " Home" or " Away" or some variation
thereof, the test will always fail.

If the data are clean and we still haven't got it, brew a
cup and post back!

Enjoying this immensely! BTW, I'm in US Central Daylight
time (GMT-5). I get the idea you're not - problem?

hcj
-----Original Message-----
Hi again. Thanks for your help. I have pasted the query
below, but It's a bit jumbled up, but, if you start at
East Lancs (which is the name of one of the leagues) and
work accoss, you might be able to figure it out. The next
line starts with Turton. I think we are getting there,
the only problem now is if you look at the first mills,
you will see that is has been allocated 3 point instead
of 2, because we are at home. All the rest are O.K.
Thanks again
Harvey
League AwayTeam Date HomeOrAaway
Home Score Away Score Expr1
East Lancs Greenhill Bolwling Club 04-Apr-04
Away 116 90 2
Turton Edgworth 05-Apr-04 Home
196 136 2
District Bar Lane "C" 06-Apr-04
Home 189 165 2
Mills Bar Lane "D" 08-Apr-04 Home
206 162 3
East Lancs Colne Cricket Club 11-Apr-04
Home 125 90 2
East Lancs East Lancs Club 18-Apr-04
Away 113 110 2
Turton Bradshaw 19-Apr-04 Home
188 153 2
District Seven Stars 20-Apr-04
Home 207 135 2
Mills Railway "B" 22-Apr-04 Home
197 129 3
District Back o`th Bank 23-Apr-04
Away 202 148 3
Turton Eagley 26-Apr-04 Away 178
156 2
District Cross Guns 27-Apr-04
Away 175 158 3
Mills Cross Guns 29-Apr-04 Away
150 174 0
East Lancs Sunnybank Social Club 02-May-04
Home 124 90 2
Turton Longsight "A" 03-May-04 Home
199 90 2
District I.G.W 04-May-04 Home
208 128 2
Turton Bar Lane "B" 10-May-04 Away
202 126 2
Mills Turton "A" 13-May-04 Away
164 166 0
East Lancs St Andrews Bowling Club 16-May-04
Away 96 114 0
Turton Turton "A" 17-May-04 Home
210 117 2
Mills Little Lever CC 20-May-04 Away
183 179 3
East Lancs Turton "A" 23-May-04
Home 126 74 2
Turton Egerton 24-May-04 Away 200
141 2
District Dobbies "A" 25-May-04
Home 176 186 0
Mills Hollywood Rd 27-May-04 Home
210 98 3
Turton Dunscar 31-May-04 Home 186
148 2

-----Original Message-----
Hi again,
Now you've got me - a good puzzle! I'd love to see the
whole setup, but lacking that try the following mod of
your iif (note Mills and District are nested in an Or and
if EITHER of those is true they are And'ed with the Away
test to give a 3 if true, else 2):

Expr1: IIf([Away Score]>[Home Score],0,IIf(([League]
="Mills" Or [League]="District") And [HomeOrAway]
="Away",3,2))

If this doesn't work, then I dare not fry my brain any
more without seeing the bigger picture, maybe a sample of
data showing all the theoretical combinations with the
scores as they should be.
Your post sounds familiar - did you try this question some
time back? Any takers then?

Keep after it - you'll get it eventually!

Best Regards, hcj


-----Original Message-----
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are equal
to 3 when [HomeOrAway]="Away". I am not sure about
Click to expand...
(and
&
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))

-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)

-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there might
be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)


-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers,
Click to expand...
either
2
or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi Harvey,
Very satisfying - glad we nailed it finally! Thanks
for your patience with my confusion. You might guess that
I haven't the slightest knowledge of the game, but the
logic exercise is applicable no matter what (as long as
the rules don't change).
Part of my problem was jargon. I finally figured out
that AwayTeam means Opponent and that HomeorAway describes
the venue relative to the League name. Am a bit curious
why Mills and District get an extra point winning away.
I would be interested to learn more about the game, but
I'm afraid it will have to wait. I'll be on vacation next
week, and we're going through some heavy fiscal year
gyrations at my end. I'll do a search to see what comes
up on the web. Any URLs to point me toward?
Having a look at the data base would be very
interesting; I promise not to editorialize unless you have
some specific questions - if it ain't broke, don't fix
it! Jackhnavonavymil, at after h dots after o and before
m. You know the drill on obfuscation, I'm sure.
It's been a pleasure, Harvey. Thanks for the
challenge. Best to you from Slidell, Louisiana.

Howard
-----Original Message-----
Brilliant! Works fine, even the shortened version. Thank
you very much. I'm from Lancashire, England and the only
problem is that I can only access the internet duing
working hours 9 till 5. BTW, the game is crown green
bowls which is only played in the north of England and is
normaly played with 10 players on each side and the games
are normaly 21 up. I don't know if you are interested,
but if you are, I will email a copy of the Bowls database
to you and maybe you could give me some feedback on any
changes that could be made.
Once again thank you very much.
Harvey
-----Original Message-----
Closing in...

It LOOKS like the 2nd and 6th Mills points should be 2 as
well, because they were "Home" and outscored the away
teams (Railway "B" and Hollywood Rd). Yes? No? District
got 2 each time it was "Home" except for 0 when the "Away"
team (Dobbie's "A") outscored them.

If above is all true, try:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf(([League]="Mills" Or [League]="District")
And [HomeOrAway]="Away",3,2)))

If this is correct, I think it can be shortened to:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf([League]="Mills" Or [League]
="District",3,2)))

If this still is not right, do one other thing first:
double check the HomeorAway entries to MAKE SURE there are
no stray spaces, especially leading spaces. We are
testing for literal strings ("Home" and "Away"). If any of
the actual entries is " Home" or " Away" or some variation
thereof, the test will always fail.

If the data are clean and we still haven't got it, brew a
cup and post back!

Enjoying this immensely! BTW, I'm in US Central Daylight
time (GMT-5). I get the idea you're not - problem?

hcj
-----Original Message-----
Hi again. Thanks for your help. I have pasted the query
below, but It's a bit jumbled up, but, if you start at
East Lancs (which is the name of one of the leagues) and
work accoss, you might be able to figure it out. The next
line starts with Turton. I think we are getting there,
the only problem now is if you look at the first mills,
you will see that is has been allocated 3 point instead
of 2, because we are at home. All the rest are O.K.
Thanks again
Harvey
League AwayTeam Date HomeOrAaway
Home Score Away Score Expr1
East Lancs Greenhill Bolwling Club 04-Apr-04
Away 116 90 2
Turton Edgworth 05-Apr-04 Home
196 136 2
District Bar Lane "C" 06-Apr-04
Home 189 165 2
Mills Bar Lane "D" 08-Apr-04 Home
206 162 3
East Lancs Colne Cricket Club 11-Apr-04
Home 125 90 2
East Lancs East Lancs Club 18-Apr-04
Away 113 110 2
Turton Bradshaw 19-Apr-04 Home
188 153 2
District Seven Stars 20-Apr-04
Home 207 135 2
Mills Railway "B" 22-Apr-04 Home
197 129 3
District Back o`th Bank 23-Apr-04
Away 202 148 3
Turton Eagley 26-Apr-04 Away 178
156 2
District Cross Guns 27-Apr-04
Away 175 158 3
Mills Cross Guns 29-Apr-04 Away
150 174 0
East Lancs Sunnybank Social Club 02-May-04
Home 124 90 2
Turton Longsight "A" 03-May-04 Home
199 90 2
District I.G.W 04-May-04 Home
208 128 2
Turton Bar Lane "B" 10-May-04 Away
202 126 2
Mills Turton "A" 13-May-04 Away
164 166 0
East Lancs St Andrews Bowling Club 16-May-04
Away 96 114 0
Turton Turton "A" 17-May-04 Home
210 117 2
Mills Little Lever CC 20-May-04 Away
183 179 3
East Lancs Turton "A" 23-May-04
Home 126 74 2
Turton Egerton 24-May-04 Away 200
141 2
District Dobbies "A" 25-May-04
Home 176 186 0
Mills Hollywood Rd 27-May-04 Home
210 98 3
Turton Dunscar 31-May-04 Home 186
148 2


-----Original Message-----
Hi again,
Now you've got me - a good puzzle! I'd love to see
the
whole setup, but lacking that try the following mod of
your iif (note Mills and District are nested in an Or
and
if EITHER of those is true they are And'ed with the Away
test to give a 3 if true, else 2):

Expr1: IIf([Away Score]>[Home Score],0,IIf(([League]
="Mills" Or [League]="District") And [HomeOrAway]
="Away",3,2))

If this doesn't work, then I dare not fry my brain any
more without seeing the bigger picture, maybe a sample
of
data showing all the theoretical combinations with the
scores as they should be.
Your post sounds familiar - did you try this question
some
time back? Any takers then?

Keep after it - you'll get it eventually!

Best Regards, hcj


-----Original Message-----
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right
answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are
equal
to 3 when [HomeOrAway]="Away". I am not sure about (and
&
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))

-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)

-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there
might
be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)


-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either
2
or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hello Howard. To complex for me. My email is
(e-mail address removed).
Harvey
-----Original Message-----
Hi Harvey,
Very satisfying - glad we nailed it finally! Thanks
for your patience with my confusion. You might guess that
I haven't the slightest knowledge of the game, but the
logic exercise is applicable no matter what (as long as
the rules don't change).
Part of my problem was jargon. I finally figured out
that AwayTeam means Opponent and that HomeorAway describes
the venue relative to the League name. Am a bit curious
why Mills and District get an extra point winning away.
I would be interested to learn more about the game, but
I'm afraid it will have to wait. I'll be on vacation next
week, and we're going through some heavy fiscal year
gyrations at my end. I'll do a search to see what comes
up on the web. Any URLs to point me toward?
Having a look at the data base would be very
interesting; I promise not to editorialize unless you have
some specific questions - if it ain't broke, don't fix
it! Jackhnavonavymil, at after h dots after o and before
m. You know the drill on obfuscation, I'm sure.
It's been a pleasure, Harvey. Thanks for the
challenge. Best to you from Slidell, Louisiana.

Howard
-----Original Message-----
Brilliant! Works fine, even the shortened version. Thank
you very much. I'm from Lancashire, England and the only
problem is that I can only access the internet duing
working hours 9 till 5. BTW, the game is crown green
bowls which is only played in the north of England and is
normaly played with 10 players on each side and the games
are normaly 21 up. I don't know if you are interested,
but if you are, I will email a copy of the Bowls database
to you and maybe you could give me some feedback on any
changes that could be made.
Once again thank you very much.
Harvey
-----Original Message-----
Closing in...

It LOOKS like the 2nd and 6th Mills points should be 2 as
well, because they were "Home" and outscored the away
teams (Railway "B" and Hollywood Rd). Yes? No? District
got 2 each time it was "Home" except for 0 when the "Away"
team (Dobbie's "A") outscored them.

If above is all true, try:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf(([League]="Mills" Or [League] ="District")
And [HomeOrAway]="Away",3,2)))

If this is correct, I think it can be shortened to:

Expr1: IIf([Away Score]>[Home Score],0,IIf([HomeorAway]
="Home",2,IIf([League]="Mills" Or [League]
="District",3,2)))

If this still is not right, do one other thing first:
double check the HomeorAway entries to MAKE SURE there are
no stray spaces, especially leading spaces. We are
testing for literal strings ("Home" and "Away"). If
Click to expand...
any
of
the actual entries is " Home" or " Away" or some variation
thereof, the test will always fail.

If the data are clean and we still haven't got it,
Click to expand...
brew
a
cup and post back!

Enjoying this immensely! BTW, I'm in US Central Daylight
time (GMT-5). I get the idea you're not - problem?

hcj

-----Original Message-----
Hi again. Thanks for your help. I have pasted the query
below, but It's a bit jumbled up, but, if you start at
East Lancs (which is the name of one of the leagues) and
work accoss, you might be able to figure it out. The next
line starts with Turton. I think we are getting there,
the only problem now is if you look at the first mills,
you will see that is has been allocated 3 point instead
of 2, because we are at home. All the rest are O.K.
Thanks again
Harvey
League AwayTeam Date HomeOrAaway
Home Score Away Score Expr1
East Lancs Greenhill Bolwling Club 04-Apr-04
Away 116 90 2
Turton Edgworth 05-Apr-04 Home
196 136 2
District Bar Lane "C" 06-Apr-04
Home 189 165 2
Mills Bar Lane "D" 08-Apr-04 Home
206 162 3
East Lancs Colne Cricket Club 11-Apr-04
Home 125 90 2
East Lancs East Lancs Club 18-Apr-04
Away 113 110 2
Turton Bradshaw 19-Apr-04 Home
188 153 2
District Seven Stars 20-Apr-04
Home 207 135 2
Mills Railway "B" 22-Apr-04 Home
197 129 3
District Back o`th Bank 23-Apr-04
Away 202 148 3
Turton Eagley 26-Apr-04 Away 178
156 2
District Cross Guns 27-Apr-04
Away 175 158 3
Mills Cross Guns 29-Apr-04 Away
150 174 0
East Lancs Sunnybank Social Club 02-May-04
Home 124 90 2
Turton Longsight "A" 03-May-04 Home
199 90 2
District I.G.W 04-May-04 Home
208 128 2
Turton Bar Lane "B" 10-May-04 Away
202 126 2
Mills Turton "A" 13-May-04 Away
164 166 0
East Lancs St Andrews Bowling Club 16-May-04
Away 96 114 0
Turton Turton "A" 17-May-04 Home
210 117 2
Mills Little Lever CC 20-May-04 Away
183 179 3
East Lancs Turton "A" 23-May-04
Home 126 74 2
Turton Egerton 24-May-04 Away 200
141 2
District Dobbies "A" 25-May-04
Home 176 186 0
Mills Hollywood Rd 27-May-04 Home
210 98 3
Turton Dunscar 31-May-04 Home 186
148 2


-----Original Message-----
Hi again,
Now you've got me - a good puzzle! I'd love to see
the
whole setup, but lacking that try the following mod of
your iif (note Mills and District are nested in an Or
and
if EITHER of those is true they are And'ed with the Away
test to give a 3 if true, else 2):

Expr1: IIf([Away Score]>[Home Score],0,IIf(([League]
="Mills" Or [League]="District") And [HomeOrAway]
="Away",3,2))

If this doesn't work, then I dare not fry my brain any
more without seeing the bigger picture, maybe a sample
of
data showing all the theoretical combinations with the
scores as they should be.
Your post sounds familiar - did you try this question
some
time back? Any takers then?

Keep after it - you'll get it eventually!

Best Regards, hcj


-----Original Message-----
Hello HCJ. Thank's for trying to help. I have input the
expression below, but still not getting the right
answer.
Mills & District are equal to e & f in my earlier
explaination and only they should be equal to 3. The
problem is that all four leagues (c, d, e & f) are
equal
to 3 when [HomeOrAway]="Away". I am not sure about (and
&
or) but when I put and as you suggested, there was
nothing after the first true statement.
Thank's again for trying.
Harvey
Expr1: IIf([Away Score]>[Home Score],0,IIf([League]
="Mills" Or [League]="District" Or [HomeOrAway]
="Away",3,2))

-----Original Message-----
Oops - wrong syntax. try:
iif(a>b,0,iif(e=away and f=away),3,2)

-----Original Message-----
Hi Harvey,
This doesn't account for a=b, and it seems there
might
be
other cases, but based on your example try:

iif(a>b,0,iif(and(e=away,f=away),3,2)


-----Original Message-----
Can anyone help me with this problem. I am doing a
project which could have 3 possible answers.
1) if a>b =0 (No further action taken)
2) if b>a it could have two possible answers, either
2
or
3 depending on criteria which goes like this.
Table [League] Containing c, d, e, and f
Table [HomeorAway] Containing Home and Away
If c, d, e, and f are at home, the answer should be 2
If c and d are away, the answer should still be 2
If e and f are away, the answer should be 3.
I hope everthing is clear!!!!!
Thanks in anticipation
Harvey
.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top