Using a constant in a relationship
I am wondering if there is an easier and cleaner solution than the one I am using for the following problem.
I need to be able to create a relationship between tables where one of the fields is equal to a constant rather than a field in the other table.
I have a table of Events.
Each event in the table has a Day Of Week field (Monday, Tuesday, etc.)
I have a Layout based on the Events table.
I need to create a portal that shows just Monday events, another that shows just Tuesday events, etc.
So, I have created a relationship (self-join in this case) for each day's events.
The only way I can make this work is for EVERY record to have seven Auto-Enter fields which contain the names of the days of the weeks.
Then, my seven relationships are like the following: Events::Monday = Monday Events::Day Of Week
It seems to me there should be a better way. Is there one and I just haven't found it?
If there is, I could eliminate dozens of mostly useless Auto-Enter fields from my database.