查找和与多列的关系 [英] Lookup and Relationship with multiple columns
问题描述
我对Access完全陌生，但是我已经完成了教程，并且对Excel有一定的VBA经验，所以我应该不会绝望.
I am completely new to Access, but I've done the tutorials, and have some VBA experience with Excel, so I shouldn't be hopeless.
我还没有开发表格，现在我专注于定义和填充表以及定义关系.
I haven't progressed to forms yet, right now I'm focusing on getting the tables defined and populated, and relationships defined.
我在这里的示例中更改了实际表，但是上下文和问题仍然存在.
I've changed the actual tables in the example here, but the context and problem hold.
表1是食物.除了自动字段键外，它还有两个字段
Table 1 is Food. Other than the autofield key, it has two fields
FoodCat FoodType
Fruit banana
Fruit apple
Fruit pear
Meat lamb
Meat beef
Meet Chicken
Vegetable Broccoli
等...
在查找和关系设置中，该表由另一个表使用. 设置起来很容易，因此另一个表具有一个仅限于包含FoodCat字段中各项的条目的字段(尽管它是重复的).我还可以将下一个字段限制为带有FoodType字段的条目，但这给了我所有选择.
This table is used by another table, in a lookup and relationship setup. It is easy to set it up so the other table has a field that is restricted to entries with the items in the FoodCat Field (although it is repetitive). I can also restrict the next field to entries with the FoodType Field, but this gives me all the choices.
在新表中，我想做的就是将FoodType选项限制为具有匹配的FoodCat选项的条目.
What I'd like to do, in the new table, is restrict the FoodType choice to the entries that have a matching FoodCat choice.
因此，如果在新表中在FoodCat下选择的项是Meat，我只希望FoodType选项是羊肉，牛肉或鸡肉.
So, if the item selected on the new table under FoodCat is Meat, I only want the FoodType choices to be lamb, beef, or chicken.
在表格中甚至有可能吗?我需要使用表格来完成此操作吗?
Is this even possible within the table? Do I need to use Forms to get this done?
推荐答案
回答您的评论:
使用查找"字段时，您将在表之间强制建立新的关系.
When you use "Lookup" fields" you are forcing a new relation between the tables.
避免出现十个访问指令中列出的最佳方法(特别是:表中查找字段的弊端)是手动创建关系，然后使用它们在表单或报表中创建适当的查找"控件.
The best way to avoid the problems listed in the Ten Commandments of Access (specifically: The evils of the lookup fields in tables ) is to create the relations manually, and then use them to create the adequate "lookup" control in a form or report.
假设您有以下表格:
tblCategories
tblCategories
id | catName
---+-----------
1 | Fruit
2 | Meat
3 | Vegetable
tblFood
tblFood
id | idCat | foodName
---+-------+-------------
1 | 1 | Apple
2 | 1 | Banana
3 | 2 | Lamb
4 | 2 | Beef
5 | 3 | Broccoli
在此示例中，很显然，这两个表之间存在一对多关系，并且tblFood中的idCat字段是外键.您可以在关系"窗口中手动创建此关系.
In this example is obvious that there's a one to many relation between these two tables, and that the idCat field in tblFood is a foreign key. You can create this relation by hand in the "Relations" window.
使用以下查询，您可以正确获取foodName和catName字段(构建报告是一件好事):
With the following query you can get both the foodName and catName fields properly (a good thing to build reports):
select f.*, c.catName
from tblFood as f
inner join tblCategories as c on f.idCat = c.id;
这会抛出类似这样的信息:
This would throw something like this:
id | idCat | foodName | catName
---+-------+----------+------------
1 | 1 | Apple | Fruit
2 | 1 | Banana | Fruit
3 | 2 | Lamb | Meat
4 | 2 | Beef | Meat
5 | 3 | Broccoli | Vegetable
这是一个很好的实用查询，您可以在报表中使用...，而无需在报表中使用组合框.
which is a nice and useful query that you can use in a report... without the need of using Combo Boxes in the report.
那么:到底如何构建一个控件(例如ComboBox)，该控件仅根据catName值过滤foodName值?
So: How on earth to build a control, for example, a ComboBox, that filters only the foodName values depending on a catName value?
首先:创建表单
然后:创建一个组合框并将其放在RowSource属性中:
Then: Create a Combo Box and put this in the RowSource property:
select id, catName from tblCategories
将控件设置为具有两列和列宽:0, 10.这将使下拉列表中的id列不可见".请确保知道此组合框的名称(假设它名为comboBox01).
Set the control to have two columns, and column widths: 0, 10. This will make the id column in the drop-down list "invisible". Be sure to know the name of this combo box (let's say it is named comboBox01).
现在，创建第二个组合框，将RowSource属性保留为空，并将列宽设置为0, 10.还要确保知道第二个组合框的名称(假设它名为comboBox02).
Now, create a second combo box, keep the RowSource property empty, and set the column widths to 0, 10. Also be sure to know the name of this second combo box (let's say it is named comboBox02).
现在让VBA做您需要的事情:在comboBox01控件的属性表上，查找afterUpdate事件，单击省略号，选择"VBA代码"并编写以下内容
And now let's make VBA do what you need: On the properties sheet of the comboBox01 control, look for the afterUpdate event, clic on the ellipsis button, select "VBA code" and write the following
Sub comboBox01_afterUpdate() ' This is added automatically by the VBA editor
strSQL = "select id, foodName from tblFood where idCat=" & comboBox01.Value
comboBox02.rowSource = strSQL
comboBox02.Requery
End Sub ' This is added automatically by the VBA editor
在表单中使用查找控件"(无查找字段")时非常有用.它们可以为您的数据库用户(包括您在内)节省很多麻烦.但是我强烈建议您避免直接在表中创建它们.
希望这会有所帮助.
这篇关于查找和与多列的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
