How would you set up a database to show the latest and historical use of resources?
I run a monthly photo competition. We have one to four judges score the images submitted. I’d like to be able to enter the month and year each judge participates.
When I look at my list of judges, I’d like to be able to see when the last time was they participated. I’d also like to be able to see the history of how often they’ve participated over at least a few years. I don’t want to have the same judges participating too often, but I also want to be able to see who has been available if I need to replace someone in a hurry.
What kind of field or fields would I need to set up in my database of judges to be able to quickly see this information? Is there a field type where I can enter a list of participation dates?
I’d set this up as two linked tables. One table is Competitions, which has a record for each competition (January 2021, February 2021, Special Valentine’s Day competition) and a date.
The other table could be Judges.
Then you create a field in the Judges table where for each judge you select each competition that they judged (Here’s how you create a linked field). Then when you look at a judge, you can see at a glance which competitions they’ve judged.
As a bonus, when you look at the competition table, you’ll see which judges judged that month.