查询与关系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具有内置的查找向导，允许用户将表字段与另一个表的特定字段绑定，例如将 cityID 从 tblVoyage 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.
所以，我想要：
- 要了解何时应该使用Access的查找向导和定义表之间的关系。
- 如何正确地最小化用户必须键入数据的次数，而不是从组合框中选择所需的项目。
推荐答案
这里的一般共识似乎是应该避免Lookup字段。他们真的只是一个简单的适当的查找表，并隐藏在表级真正发生了什么。例如，假设您的[Size]的值为'Small'，'Medium'和'Large'的Lookup字段。当你看桌子时，你会看到单词，但是表格真的包含像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.)
对于更多的混乱，Lookup向导提供了一个允许多个选择选项，因此一个字段可以包含多个值（一种）。这显然是添加的，所以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.
组合框向导将引导您完成将组合框链接到其记录源（查找表）并将其值绑定到数据表中的字段的过程。例如，假设您已经为[代理商]
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]表创建一个新表单作为其记录来源。当您向向导应该运行的形式添加组合框时，请问您希望组合框获得其值？您选择我想要组合框从另一个表或查询获取值。
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 将是数字 [代理]。[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屋！
