MS Access中多值字段的替代 [英] Alternative to multi-valued fields in MS Access

问题描述

相关问题:多值字段是个好主意吗?

我知道多值字段类似于多对多关系.在MS Access应用程序中替换多值字段的最佳方法是什么? 我有一个具有多值字段的应用程序.我不确定如何完全消除这些错误并以单值字段的形式实现完全相同的逻辑?

I know that multi-valued fields are similar to many-to-many relationship. What is the best way to replace multi-valued fields in an MS Access application? I have an application that has multi-valued fields. I am not sure how exactly to do away with those and implement exactly same logic in the form of fields that are single-valued?

当我想将多值关系移动到单值关系时，表关系的实现是什么.

What would be the implementation in terms of table-relationships when I want to move a multi-valued relationship to single-valued one.

谢谢.

推荐答案

以下内容可能比您需要的要详细得多，但是它是针对初学者的.假设您有一个表MainTable:

The following is probably far more detailed than you need, but it is intended for a beginner. Let us say you have a table, MainTable:

ID -> Numeric, primary key
Title -> Text
Surname -> Text
Address -> Text
Country -> Numeric

您可能需要一个标题列表和要选择的国家/地区.

You will probably want a list of titles and countries from which to select.

对于标题，将信息存储在表中的字段中并不是最坏的事情，因为您只有一列并且数据不太可能更改，并且您可能不会创建查询使用数据.

In the case of Title, it would not be the worst thing to store the information in a field in a table, because you have a single column and the data is unlikely to change, and you probably will not be creating a query using the data.

国家是另一回事，按照惯例，您将存储一个数字并拥有一个查询表.在这种情况下，人们倾向于使用多值字段.

Country is a different story, conventionally you would store a number and have a look-up table. It is the kind of situation where people are tempted to use a multi-value field.

但是，约定要容易得多.为国家/地区添加另一个表格:

However, convention is a lot easier. Add another table for country:

ID -> Numeric, primary key
Country -> Text

您可能想调用主表CountryID中的相关字段.现在，您可以在关系窗口中创建一个关系，以显示国家/地区与MainTable的关系:

You might like to call the related field in the main table CountryID. You can now create a relationship in the relationship window showing how Country relates to MainTable:

您会看到已选择强制引用完整性"，这意味着您必须在国家/地区"字段的国家/地区"表中包含空值或国家/地区.

You can see that Enforce Referential Integrity is selected, which means that you must have null or a country from the countries table in the CountryID field.

要查看数据，可以创建查询:

To view the data, you can create a query:

SELECT 
    MainTable.ID, 
    MainTable.Title, 
    MainTable.Surname, 
    MainTable.Address, 
    Country.Country
FROM Country 
INNER JOIN MainTable 
ON Country.ID = MainTable.CountryID;

但是要点是要有一个允许数据输入的表格.您可以使用向导创建表单，但是此后，您需要右键单击CountryID并将其更改为组合框，或者使用向导添加组合框或列表框.选项2可能是最简单的.以下是向导中的大多数步骤:

But the main point is to have a form that allows data entry. You can create a form using the wizards, but after that, you either need to right-click CountryID and change it to a combobox or add a combobox or listbox using the wizard. Option 2 is probably the easiest. Here are most of the steps from the wizard:

您现在可以在表格上找到国家/地区的下拉列表.

You now have a dropdown list of countries on your form.

另请参见:创建表单以在其中添加记录多个表

在Access 2010中，当用户输入可能值表中不存在的数据时，有一些新的方法可将值添加到组合中.在以前的版本中(尽管我不确定2007年如何)，您将使用不在列表中"事件将项目添加到查找表中；在2010年，您可以选择将列表项编辑"表单添加到属性表中.

In Access 2010, there are new ways of adding values to combos when the user enters data that does not exist in the table of possible values. In previous versions (although I am not sure about 2007), you would use the Not In List event to add items to a look-up table, in 2010, you have a choice of adding a List Items Edit form to the property sheet.

这篇关于MS Access中多值字段的替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！