By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

How to build this field expression

P: n/a
I've 2 tables as below (1 to 1 relationship between ID's)

tableGames
ID | Home | Away | Result|
1 | team_A | team_B | H |
2 | team_C | team_D | D |
3 | team_E | team_F | A |
4 | team_G | team_H | H |
....
(H=home team win, D=Draw, A=Away team win)

tableOdds
ID| Home | Draw | Away |
1 | 1,40 | 3,30 | 6,00 |
2 | 1,15 | 2,40 | 9,00 |
3 | 2,60 | 3,00 | 3,20 |
4 | 1,90 | 3,10 | 2,50 |
....

I want to build an expression to achieve a result table like below;
tableOddResults
ID | Home | Away | Odd |
1 | team_A | team_B | 1,40 |
2 | team_C | team_D | 2,40 |
3 | team_E | team_F | 3,20 |
4 | team_G | team_H | 1,90 |

In other words I want to get the odd value from tableOdds corresponding to
the game result.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Your problem here stems from the fact that your TableOdds table is not
normalised. If you had:

ID | Type | Val |
1 | H | 1,40 |
etc.

then your query becomes trivial.

P
"King Jeremy" <vi********@hotmail.com> wrote in message
news:bn**********@list.ege.edu.tr...
I've 2 tables as below (1 to 1 relationship between ID's)

tableGames
ID | Home | Away | Result|
1 | team_A | team_B | H |
2 | team_C | team_D | D |
3 | team_E | team_F | A |
4 | team_G | team_H | H |
...
(H=home team win, D=Draw, A=Away team win)

tableOdds
ID| Home | Draw | Away |
1 | 1,40 | 3,30 | 6,00 |
2 | 1,15 | 2,40 | 9,00 |
3 | 2,60 | 3,00 | 3,20 |
4 | 1,90 | 3,10 | 2,50 |
...

I want to build an expression to achieve a result table like below;
tableOddResults
ID | Home | Away | Odd |
1 | team_A | team_B | 1,40 |
2 | team_C | team_D | 2,40 |
3 | team_E | team_F | 3,20 |
4 | team_G | team_H | 1,90 |

In other words I want to get the odd value from tableOdds corresponding to
the game result.

Nov 12 '05 #2

P: n/a
If your table names and the field names are exactly as you mentioned, paste
the following into a new query.

SELECT tableGames.ID, tableGames.Home, tableGames.Away, tableGames.Result,
Format(IIf([Result]="H",[tableOdds].[Home],IIf([Result]="D",
[tableOdds].[Draw],[tableOdds].[Away])),"Fixed") AS Odds
FROM tableGames INNER JOIN tableOdds ON tableGames.ID = tableOdds.ID;

If there is no value in the result field it will always pull the value from
the away odds without placing in another nexted IIf statement.

Some one else might have a better way to do this with striaght SQL without
using nexted IIf statements

Stewart
"King Jeremy" <vi********@hotmail.com> wrote in message
news:bn**********@list.ege.edu.tr...
I've 2 tables as below (1 to 1 relationship between ID's)

tableGames
ID | Home | Away | Result|
1 | team_A | team_B | H |
2 | team_C | team_D | D |
3 | team_E | team_F | A |
4 | team_G | team_H | H |
...
(H=home team win, D=Draw, A=Away team win)

tableOdds
ID| Home | Draw | Away |
1 | 1,40 | 3,30 | 6,00 |
2 | 1,15 | 2,40 | 9,00 |
3 | 2,60 | 3,00 | 3,20 |
4 | 1,90 | 3,10 | 2,50 |
...

I want to build an expression to achieve a result table like below;
tableOddResults
ID | Home | Away | Odd |
1 | team_A | team_B | 1,40 |
2 | team_C | team_D | 2,40 |
3 | team_E | team_F | 3,20 |
4 | team_G | team_H | 1,90 |

In other words I want to get the odd value from tableOdds corresponding to
the game result.



Nov 12 '05 #3

P: n/a
You need to do a join with an iif

Select tableGames.ID, tableGames.Home, tableGames.Away,
iif(tableGames.Result = 'H', tableOdds.Home,
iif(tableGames.Result='D',table.Odds.Draw,
iif(tableGames.Result='A', tableOdds.Away, 'Unknown'))) as Odd
From tableGames Left Join tableOdds On tableGames.ID = tableOdds.ID
"King Jeremy" <vi********@hotmail.com> wrote in message news:<bn**********@list.ege.edu.tr>...
I've 2 tables as below (1 to 1 relationship between ID's)

tableGames
ID | Home | Away | Result|
1 | team_A | team_B | H |
2 | team_C | team_D | D |
3 | team_E | team_F | A |
4 | team_G | team_H | H |
...
(H=home team win, D=Draw, A=Away team win)

tableOdds
ID| Home | Draw | Away |
1 | 1,40 | 3,30 | 6,00 |
2 | 1,15 | 2,40 | 9,00 |
3 | 2,60 | 3,00 | 3,20 |
4 | 1,90 | 3,10 | 2,50 |
...

I want to build an expression to achieve a result table like below;
tableOddResults
ID | Home | Away | Odd |
1 | team_A | team_B | 1,40 |
2 | team_C | team_D | 2,40 |
3 | team_E | team_F | 3,20 |
4 | team_G | team_H | 1,90 |

In other words I want to get the odd value from tableOdds corresponding to
the game result.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.