显示所有记录指定字段筛选在Access 2010表 [英] Show All Records For Given Field Filter In Access 2010 Table

问题描述

希望这将非常有意义。我在访问2010一表，其中包含供应商的名单和他们的观点在供应商和联系，我的工作的。所述的POC变化中的序号，从1-4的任意位置到这一点。该表设置，让每个POC是在单独一行。​​

供应商可以有一个接触，但工作可能有三种不同的接触，反之亦然。

当我选择的一种形式从一个组合框的值我希望发生的是，所有相关的POC需要显示一个经过逐一循环，而不是。

例如，Supplier1有两的POC在他们的工厂，我们有三个在我们的设施。我想有组合框找到Supplier1在表中，然后显示所有该供应商的接触（他们的工厂和我们）在一个文本框。

的用户将能够编辑联系人信息，并且如果它是不是太困难，将能够添加/删除联系人

我敢肯定，类似这样的问题已经问过，但我一直无法话，它正确地发现，通过谷歌搜索/本网站的解决方案。我足以与VBA舒适的使用，如果需要的，但我绝不是专家。我完全不熟悉SQL，并想避免，如果在所有可能的。

往那个方向

我必须小心我提供任何数据，但会做什么，我可以的，如果你需要看到的数据或类似的东西。

 公司code部分供应商联系采购联系QC联系
Ajin AKVN帕特里克勇杰克
Ajin AKVN大通勇杰克
奥托立夫AMNP安全带达里尔·詹姆斯·刘易斯
博世AG48 Hancheul凯文·
Carlex AKJ5 QTR眼镜鲍勃·乐扎克
大陆ANKC雅各
KSR C03A05刹车踏板何塞·保罗·戴维
KSR C03A05刹车踏板何塞·保罗·加里
KSR C03A05刹车踏板何塞·保罗·史蒂芬
KSR AG5Z油门踏板杰克·保罗·戴维
KSR AG5Z油门踏板杰克·保罗·加里
KSR AG5Z油门踏板杰克·保罗·史蒂芬
KSR AG5Z油门踏板科里保罗·戴维
KSR AG5Z油门踏板科里保罗·加里
KSR AG5Z油门踏板科里保罗·史蒂芬
 

解决方案

您的表需要重标准化（例如见的什么是正常化（或标准化）？或 http://r937.com/relational.html ）

我建议（注：我不知道供应商/ code /部件关系）：

   -  tSupplier
供应商ID SupplierName
1 Ajin
2 KSR

 -  tParts
PARTID供应商ID code部分
1 1 AKVN
2 2 C03A05刹车踏板

 -  tContactTypes
typeid的类型
1供应商
2采购
3 QC

 -  tContacts
使用ContactID供应商ID typeid的联系人姓名
1 1 1帕特里克
2 1 1大通
3 1 2勇
4 1 3杰克
 

等。 每个表的第一列是在主键，自动编号字段。 所有其它ID列是外键，链接到一个父表。

现在你可以有一个组合框的供应商，这给供应商ID。
有了这一点，你可以过滤联系人，并显示在一个数据表子窗体。
无论是在同一个表，与ContactTypes为列，或在三个子表单，每个过滤一个ContactType。

要能够添加新的联系人，使用 BeforeInsert 事件，分配当前的供应商ID。

Hopefully this will make sense...I have a table in Access 2010 that contains a list of suppliers and their point of contacts at the supplier and where I work. The POCs vary in number, anywhere from 1-4 up to this point. The table is set up so each POC is on a separate line.

The supplier could have one contact but work could have three different contacts and vice versa.

What I want to happen is when I select a value from a combobox on a form, all the related POCs need to be shown instead of cycling through them one by one.

For example, Supplier1 has two POCs at their facility and we have three at our facility. I would like to have the combobox find Supplier1 in the table and then show all the contacts for that supplier (their facility and ours) in a textbox.

The user will be able to edit the contact information and, if it is not too difficult, would be able to add/delete a contact.

I'm sure a question similar to this one has been asked before, however I have been unable to word it correctly to find a solution through google searches/this website. I'm comfortable enough with VBA to use that if required but am by no means an expert. I am completely unfamiliar with SQL and would like to avoid going that direction if at all possible.

I have to be careful with any data I provide but will do what I can if you need to see the data or anything like that.

Supplier    Code    Part    Supplier Contact   Procurement Contact  QC Contact
Ajin        AKVN            Patrick                 Yong                 Jack   
Ajin        AKVN            Chase                   Yong                 Jack   
Autoliv     AMNP   Seatbelt Daryl                   James                Lewis  
Bosch       AG48            Hancheul                Kevin           
Carlex      AKJ5  QTR Glasses  Bob                  Joy                   Zack  
Continental ANKC            Jacob           
KSR       C03A05 Brake Pedal Jose                   Paul                 David
KSR       C03A05 Brake Pedal Jose                  Paul                  Gary
KSR      C03A05  Brake Pedal Jose                  Paul                  Steven
KSR      AG5Z   Accelerator Pedal Jack             Paul                  David
KSR      AG5Z   Accelerator Pedal Jack             Paul                  Gary
KSR     AG5Z    Accelerator Pedal Jack             Paul                 Steven
KSR     AG5Z    Accelerator Pedal Cory             Paul                 David
KSR     AG5Z    Accelerator Pedal Cory             Paul                 Gary
KSR     AG5Z    Accelerator Pedal Cory             Paul                 Steven

解决方案

Your table needs heavy normalization (see e.g. What is Normalisation (or Normalization)? or http://r937.com/relational.html )

I would suggest (Note: I'm not sure about the Supplier/Code/Part relation) :

- tSupplier
SupplierID   SupplierName
1            Ajin
2            KSR

- tParts
PartID     SupplierID   Code      Part
1          1            AKVN
2          2            C03A05    Brake Pedal 

- tContactTypes
TypeID    Type
1         Supplier 
2         Procurement 
3         QC

- tContacts
ContactID  SupplierID  TypeID  ContactName
1          1           1       Patrick
2          1           1       Chase
3          1           2       Yong
4          1           3       Jack

and so on. The first column of each table is the primary key, an autonumber field. All other ID columns are foreign keys, linking to a parent table.

Now you can have a combobox for the Supplier, which gives the SupplierID.
With that, you can filter the Contacts and show them in a datasheet subform.
Either all in one table, with the ContactTypes as column, or in three subforms, each filtered by one ContactType.

To be able to add new contacts, use the BeforeInsert event to assign the current SupplierID.

这篇关于显示所有记录指定字段筛选在Access 2010表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！