Case / If Perhaps

[Poruchan]

Very fundamental concept query. This might not belong here, but I'm here to discover if I am thinking 'case' and 'if' wrong.

Quite often I come to a situation with 2 tables (something like this):

Table A fields: ID_A, RaceID, Horse Name, Time, Finish

Table B fields: ID_B, RaceID, Winner, Second

In Table B I want to populate 'Winner' and 'Second'.  I would do a simple look-up for 'Winner', but then I go for 'Second' and I start bumbling around and thinking like this (just English, not FM-ese): If Table A RaceID = Table B RaceID and Fin = 2 then give me the Horse Name. Then I start fumbling around. I check the manual, the Missing Manual, videos ... and then I start thinking that this is such a simple task, I must be thinking it wrong. Am I?

 

 

 

Edited October 15, 2015 by Poruchan

[doughemi]

Probably easier to do with a relationship than with a calc.

Poruchan Horses.fmp12.zip

[Poruchan]

Thanks, Doug

I reproduced it and I have the 2nd place horses now. My new recent goal is to understand why these things work rather than just ask for and get solutions. As what you did is not obvious to me, Im working up a few questions.  

[comment]

In Table B I want to populate 'Winner' and 'Second'. 

What would be the purpose of that? The information already exists in the other table. If you're looking for fundamental concepts, here's the most fundamental one: in a normalized relational database, each fact is stored in one place only.

[ggt667]

 

In Table B I want to populate 'Winner' and 'Second'.

I believe from context that populate is not the word, rather sort comes to mind.

Edited October 15, 2015 by ggt667