How to build this field expression

[King Jeremy]

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.

[Phobos]

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.

[Stewart Allen]

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.

[MeadeR]

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.