使用AutoNumber外键将数据输入表中 [英] Entering Data into Table with AutoNumber Foreign Key

问题描述

我遇到了一个问题，它最基本和最内容的形式是由两个表和一个关系组成。

I've run into a bit of an issue which at its most basic and gutted form is composed of two tables and a relationship.

假设你有两个表，一个持有公司名单，另一个持有员工。 Table Company有两个字段（相关），companyName（短文本）和companyID（AutoNumber）。也许两家公司将拥有相同的名称，或者一家公司需要
来轻松更改公司名称，因此companyName是正常字段，而companyID是主键。

Suppose you have two tables, one holding a list of companies, the other holding the employees. Table Company has two fields (that are relevant), companyName (Short Text), and companyID (AutoNumber). Perhaps two companies will have the same name, or one needs to easily change the name of a company, and so companyName is a normal field while companyID is the primary key.

第二个表Employee包含employeeName（短文本）和它们工作的companyID（AutoNumber）。它们是主要关键。为了清楚起见，Employee.companyID是一个引用Company.companyID的外键

The second table, Employee, contains employeeName (Short Text) and the companyID (AutoNumber) that they work for. Together, they are the primary key. Just to be clear, Employee.companyID is a foreign key that references Company.companyID

很容易，直接进入表格或使用表格，我可以向公司添加数据。 

Easily, directly into the table or using a form, I can add data to Company. 

但是，我无法选择Employee.companyID来添加员工。 

However, I have no ability to select the Employee.companyID to add employees. 

- 我无法更改Employee.companyID的数据类型，因为那时关系不起作用（"关系必须在具有相同数据类型的相同数量的字段上）

- I can't change the datatype of Employee.companyID, as then the relationship doesn't work ("Relationship must be on the same number of fields with the same data types)

- 两种类型的自动编号都没有帮助（增量或随机）

- Neither type of AutoNumber helps (increment or random)

- 我能想到的唯一解决方案是要么没有外键（不理想但空间不是很重要），要么不使用AutoNumber并在VBA中生成值（听起来过于考虑这似乎是一个简单/普遍的问题）

- The only solutions I can think of are to either not have the foreign key (not ideal but space isn't much of a concern), or not use AutoNumber and generate the value within VBA (sounds excessive considering this seems like a simple/common enough issue)

希望，我忽略了一些显而易见的事情。谢谢！

Hopefully, I'm overlooking something obvious. Thanks!

推荐答案

我使用自动编号字段employeeID作为Employee表的主键。

I'd use an AutoNumber field employeeID as primary key of the Employee table.

要输入数据，请创建一个主表单基于Company，以及基于Employee的子表单，使用companyID链接它们。创建新记录时，Access将自动填写Employee子表单中的companyID。

To enter data, create a main form based on Company, and a subform based on Employee, with companyID to link them. Access will automatically fill in companyID in the Employee subform when you create a new record.

这篇关于使用AutoNumber外键将数据输入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！