Automatically Set Which Period a Date Falls In

[Rupert85]

Hi All, bit of a novice here.

I have a table that defines the start and end dates for football seasons. In my table Matches, I want my 'Season_ID' field to automatically show the 'SeasonDescription' for which ever season it is when I insert the 'MatchDate'. Can anyone advise me how to do this? Don't imagine it can be that difficult but I cant find anything using google.

Table: Seasons

• Season_ID
  
• SeasonDescription [iE: SEASON 2011/12]
  
• SeasonStartDate [iE: 01/JUN/2011]
  
• SeasonEndDate [iE: 30/MAY/2012]
  

Table: Matches

• Match_ID
  
• MatchDate
  
• Season_ID
  

[comment]

You can define a relationship as:

Matches::MatchDate ≥ Seasons::SeasonStartDate

AND

Matches::MatchDate ≤ Seasons::SeasonEndDate

then place the field Seasons::SeasonDescription on the layout of Matches.

If you prefer to associate a match with a season through the date, then the field Matches::SeasonID is redundant.

[Rupert85]

Thank you, make perfect sense.

However having created the field, I'm not 100% sure on how I'd populate it. Would I use a value list or is there something else I can set to do it for me?

[comment]

It only makes to populate it if you are going to use it - i.e. if the relationship between the tables will be:

Matches::SeasonID = Seasons::SeasonID

[Rupert85]

It only makes to populate it if you are going to use it - i.e. if the relationship between the tables will be:

Matches::SeasonID = Seasons::SeasonID

Sorry - I think my poor use of the proper terminology has made my last post unclear.

I have created the date relationship as you described. And put the SeasonDescription on the Matches layout. But its showing up empty and I cant work out how to populate it?

Apologies if I seem a little off it. Appreciate the help.

[comment]

You are not supposed to populate it (in the Matches table). It should be showing data from the related record in Seasons. If it doesn't, then either the relationship doesn't work or there is no data in the field (in the Seasons table).

[Rupert85]

You are not supposed to populate it (in the Matches table). It should be showing data from the related record in Seasons. If it doesn't, then either the relationship doesn't work or there is no data in the field (in the Seasons table).

Oops. I hadn't set the dates correctly - thank you.