ms-access - 如何在 Access 中为表构建自定义主键？

Question

我正在将 csv 文件中的数据导入到如下所示的表中：

+------------+------------------------+----------------+
|   date_1   |      tournament_1      |     misc_1     |
+------------+------------------------+----------------+
| 01/01/2020 | ATP-ACAPULCO           | random_data_1  |
| 01/01/2020 | ATP-ACAPULCO           | random_data_2  |
| 02/01/2020 | ATP-ACAPULCO           | random_data_3  |
| 01/01/2020 | CALGARY-CHALLENGER-MEN | random_data_4  |
| 02/01/2020 | CALGARY-CHALLENGER-MEN | random_data_5  |
| 02/01/2020 | CALGARY-CHALLENGER-MEN | random_data_6  |
| 03/01/2020 | CALGARY-CHALLENGER-MEN | random_data_7  |
| 03/01/2020 | CALGARY-CHALLENGER-MEN | random_data_8  |
| 01/01/2021 | ATP-ACAPULCO           | random_data_9  |
| 01/01/2021 | ATP-ACAPULCO           | random_data_10 |
| 02/01/2021 | ATP-ACAPULCO           | random_data_11 |
| 02/01/2021 | CALGARY-CHALLENGER-MEN | random_data_12 |
| 03/01/2021 | CALGARY-CHALLENGER-MEN | random_data_13 |
+------------+------------------------+----------------+

我需要能够将此表链接到另一个链接表，该表具有来自另一个系统的锦标赛列表：

+------+----------------------+
| id_2 |     tournament_2     |
+------+----------------------+
|  123 | Mexico-Acapulco-2020 |
|  456 | Canada-Calgary-2020  |
|  789 | Mexico-Acapulco-2021 |
| 1011 | Canada-Calgary-2021  |
+------+----------------------+

由于第二个表是链接的，所以我的计划是建立第三个“交叉引用”表：

+------+----------------------+------------------------+
| id_2 |     tournament_2     |      tournament_1      |
+------+----------------------+------------------------+
|  123 | Mexico-Acapulco-2020 | ATP-ACAPULCO           |
|  456 | Canada-Calgary-2020  | CALGARY-CHALLENGER-MEN |
|  789 | Mexico-Acapulco-2021 | ATP-ACAPULCO           |
| 1011 | Canada-Calgary-2021  | CALGARY-CHALLENGER-MEN |
+------+----------------------+------------------------+

但是，我需要独特的比赛对，而我不需要。

为了解决这个问题，我可以为第一个表构建一个主键，如下所示：

+------------+------------------------+----------------+-----------------------------+
|   date_1   |      tournament_1      |     misc_1     |      tournament+year_1      |
+------------+------------------------+----------------+-----------------------------+
| 01/01/2020 | ATP-ACAPULCO           | random_data_1  | ATP-ACAPULCO-2020           |
| 01/01/2020 | ATP-ACAPULCO           | random_data_2  | ATP-ACAPULCO-2020           |
| 02/01/2020 | ATP-ACAPULCO           | random_data_3  | ATP-ACAPULCO-2020           |
| 01/01/2020 | CALGARY-CHALLENGER-MEN | random_data_4  | CALGARY-CHALLENGER-MEN-2020 |
| 02/01/2020 | CALGARY-CHALLENGER-MEN | random_data_5  | CALGARY-CHALLENGER-MEN-2020 |
| 02/01/2020 | CALGARY-CHALLENGER-MEN | random_data_6  | CALGARY-CHALLENGER-MEN-2020 |
| 03/01/2020 | CALGARY-CHALLENGER-MEN | random_data_7  | CALGARY-CHALLENGER-MEN-2020 |
| 03/01/2020 | CALGARY-CHALLENGER-MEN | random_data_8  | CALGARY-CHALLENGER-MEN-2020 |
| 01/01/2021 | ATP-ACAPULCO           | random_data_9  | ATP-ACAPULCO-2021           |
| 01/01/2021 | ATP-ACAPULCO           | random_data_10 | ATP-ACAPULCO-2021           |
| 02/01/2021 | ATP-ACAPULCO           | random_data_11 | ATP-ACAPULCO-2021           |
| 02/01/2021 | CALGARY-CHALLENGER-MEN | random_data_12 | CALGARY-CHALLENGER-MEN-2021 |
| 03/01/2021 | CALGARY-CHALLENGER-MEN | random_data_13 | CALGARY-CHALLENGER-MEN-2021 |
+------------+------------------------+----------------+-----------------------------+

然后我可以创建一对一的配对，如下所示：

+------+----------------------+-----------------------------+
| id_2 |     tournament_2     |    tournament_1 + year_1    |
+------+----------------------+-----------------------------+
|  123 | Mexico-Acapulco-2020 | ATP-ACAPULCO-2020           |
|  456 | Canada-Calgary-2020  | CALGARY-CHALLENGER-MEN-2020 |
|  789 | Mexico-Acapulco-2021 | ATP-ACAPULCO-2021           |
| 1011 | Canada-Calgary-2021  | CALGARY-CHALLENGER-MEN-2021 |
+------+----------------------+-----------------------------+

但是，我无法通过计算字段创建密钥，因为我无法链接它，所以我不得不依靠一些 VBA 来在导入时创建它。

我在这里错过了更简单或更优雅的解决方案吗？

标签： ms-access