查找与关系 Microsoft Access [英] Look-up vs relationship Microsoft Access

问题描述

我正在开发基于 Microsoft Access 2013 的信息系统.客户的需求之一是通过使用具有可用值的组合框来简化数据输入过程.

I'm developing a Microsoft Access 2013 based information system. One of the client's demands was to simplify the data entry process by using combo box with available values.

例如，客户端要求让用户从组合框中选择代理名称，而不是输入 agentID，与其他类似字段的逻辑相同.

For example, instead of entering agentID the client asked to let the user choose agent name from the combo box, the same logic with other similar fields.

简而言之:
我需要尽可能避免需要输入值 ID 并让用户从组合框中选择它们.

In brief:
I need to avoid as much as possible the need to enter the values ID and let to user choose them from the combo box.

Microsoft Access 有一个内置的查找向导，允许用户将表字段与另一个表中的特定字段绑定，例如通过查找向导将 tblVoyage 中的 cityID 与 tblCities/cityID 链接，并让用户从组合中选择城市框，而不是在字段中输入特定的城市 ID.

Microsoft Access has a built-in lookup wizard that allows to user to bind the table field with specific field from another table, e.g. to link cityID from tblVoyage with tblCities/cityID by lookup wizard, and let user choose the city from the combo box and not by typing specific city ID into the field.

一切看起来都不错，但有一个令人困惑的时刻.在 DB 课程中我了解到，为了构建一个数据库并使用它，我们必须定义表之间的关系(1:1、1:M、M:N)，但如果我这样做，我就不能使用查找向导，因为我已经定义了表之间的关系.因此，用户必须手动输入所有 ID，而不是从组合框中选择它们.

Everything looks great, but there one confusing moment. During DB course I learnt, that in order to build a database and work with it, we have to define relationships between the tables (1:1, 1:M, M:N), but if I do it I can't use lookup wizard, because I've already defined relationships between tables. And, as a result, the user has to type all IDs manually, instead of choose them from the combo box.

所以，我想要:

1. 了解我应该在何时使用 Access 查找向导以及何时定义表之间的关系.
2. 如何正确减少用户必须键入数据而不是从组合框中选择所需项目的次数.

推荐答案

这里的普遍共识似乎是应该避免查找字段.它们实际上只是拥有正确"查找表的捷径，并且它们隐藏了表级别真正发生的事情.例如，假设您有一个 [尺寸] 的查找字段，其值为小"、中"和大".当您查看表格时，您会看到单词，但该表格很可能确实包含数字，例如 1、2 和 3.您转到

The general consensus here seems to be that Lookup fields should be avoided. They are really just a short-cut to having a "proper" lookup table and they hide what is really going on at the table level. For example, say you have a Lookup field for [Size] with values 'Small', 'Medium' and 'Large'. When you look at the table you see the words, but the chances are good that the table really contains numbers like 1, 2, and 3. You go to

UPDATE tblName SET Size="Large"

并且查询失败，因为您实际上需要做的是

and the query fails because it what you actually need to do is

UPDATE tblName SET Size=3

(当您维护一个适当的"单独查找表时，主表中的字段会显示实际上字段中的内容:查找表中所选项目的 PK 值.您仍然可以将您的表单设计为具有由查找表填充并绑定到主表中的字段的组合框.)

(When you maintain a "proper" separate lookup table your field in the main table shows what is actually in the field: the PK value for the selected item in the lookup table. You can still design your forms to have a combo box populated by the lookup table and bound to a field in the main table.)

更令人困惑的是，查找向导提供了允许多项选择"选项，因此一个字段可以包含多个值(某种程度).这显然是为了让 Access 可以更好地与 SharePoint 配合使用，而那确实是唯一应该使用该功能的时候.在绝大多数其他情况下，最好显式维护一个单独的子表来存储多项选择.

For even more confusion, the Lookup wizard offers an "allow multiple selections" option so a field can hold more than one value (sort of). That was apparently added so Access could work better with SharePoint, and that is really the only time when that feature should be used. In the overwhelming majority of other cases it's better to explicitly maintain a separate child table to store the multiple selections.

组合框向导将指导您完成将组合框链接到其记录源(查找表)并将其值绑定到数据表中的字段的过程.例如，假设您已经为 [Agents]

The Combo Box Wizard will guide you through the process of linking your combo box to its record source (the lookup table) and binding its value to a field in your data table. For example, say you have already set up a lookup table for [Agents]

ID  AgentName
--  ---------
 1  Gord     
 2  Angie    

...以及 [Accounts] 的数据表

...and a data table for [Accounts]

ID  agentID  AccountName
--  -------  -----------

您使用 [Accounts] 表作为其 Record Source 创建一个新表单.当您将组合框添加到表单时，向导应该运行并询问您您希望组合框如何获取其值?".您选择我希望组合框从另一个表或查询中获取值."

You create a new Form with the [Accounts] table as its Record Source. When you go to add a Combo Box to the form the wizard should run and ask you "How do you want your combo box to get its values?". You choose "I want the combo box to get the values from another table or query."

在下一步中，您选择 [Agents] 表:

In the next step you choose the [Agents] table:

接下来告诉向导您要显示 [AgentName]:

Next you tell the wizard that you want to display the [AgentName]:

选择排序顺序(如果需要)后，您可以确认列宽.启用隐藏键列(推荐)".

After you choose a sort order (if desired) you get to confirm the column width(s). Leave "Hide key column (recommended)" enabled.

最后，您可以选择复选框的值会发生什么变化.这是您将其绑定"到 [Accounts] 表中的 [agentID] 字段的地方:

Finally, you get to choose what happens to the check box's value. This is where you "bind" it to the [agentID] field in the [Accounts] table:

请注意，组合框将显示 [agentName] 供用户选择，但其 .Value 将是数字 [Agents].[ID]，这就是将存储在 [Accounts].[agentID] 中的内容.

Note that the combo box will display the [agentName] for the user to select, but its .Value will be the numeric [Agents].[ID], and that is what will be stored in [Accounts].[agentID].

这篇关于查找与关系 Microsoft Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！