Questioning my logic regarding relationship
I'm working on a DB and have three tables defined; locations, time period and values. (see screenshot). Locations just contains names of locations and their abbreviation (short name). Time Period contains specific month & year combos which define a specific calendar period; i.e. June 2010, August 2012, etc.
The Values table will contain statistical values based on a specific location and a specific time period; i.e. Location1 & July 2013. Each record in Values will have this specific combo which I have delineated to a field named time_loc_period, BAR_01_2011. Only one record in Values can use this time_loc_period so it is unique.
The idea for the data entry layout is to have the user come into the layout from a button based menu which will automatically fill in the location depending on the button pressed (each location is going to have it's own layout since some don't need as many statistical fields as others). The next step would be for the user to select the month and year that they are entering data for. I tried just having the user choose a month/year time period from a dropdown of available possibilities from the Time period table but the list is extremely long.
Ideally I think it might be easier on the user to pick the month and year separately from 2 fairly short value lists, but I'm not sure how to then associate the correct time period based on their choices.
For example user chooses February from month list and 2015 from year list. This should then be able to link to say record #20 in the Time Period table whose calendar_period = February 2015.
Any help would be greatly appreciated!
Create a calculation field to combined the user selections and then base your relationship on this new field.
The calculation would be userMonth & " " & userYear Use the name of your fields. This calculation puts a space between month and year.