Implementation of having [0,2] rows, but not more, with similar data

Question

I am implementing an access database, using MS Access 2016. I am trying to create a table that has some columns, where 2 of them are a combined foreign key.

Now, I want each value of the foreign key to be usable a maximum of 2 times, in the new table. If a user tries to use the same value, a third time, then the program should give an error.

I haven't been able to find an answer to this question anywhere else.

Original table: Field1  Field2
                  1        2
                  2        2
New table: Field1(FK) Field2(FK)PK Field3 PK
              1         2            3
              1         2            4
              1         2            5      <---- Generate error

FK - Foreign Key

PK - Primary Key

EDIT1: I want to clarify that I want Field1 and Field2 in New Table to have the required functionality.

EDIT2: To give an example of my problem, I have a basketball game (Field1: StartingTime Field2: Stadium). For this game, I need 2 teams. Therefore I have a table GameTeams. But there cannot be more than 2 teams, thus I need this limitation of 2. In other words, each Game has 2 Gameteams. Hopefully this gives a better understanding.

Answer 1

With Access 2010 and later you can accomplish your goal by using a saved query that counts the rows associated with each (Field1,Field2) pair ...

... along with a Before Change data macro on your table to check for existing related rows: