类别之间的连接 [英] Junction between categories

问题描述

我正在为自由手语翻译制作一个数据库.我有一个主题表 tblClient ，其中包含有关自由职业者客户的信息.有一个查找表 tlkClientClientType 显示各种类型的客户端(按条件分类——聋、聋/盲等).还有一个叫做 tlkClientModality 的表.此表中列出了口译员提供的不同类型的手语.您可以从客户表中选择客户的首选方式.

现在是棘手的部分.如果客户是某种类型，则某些形式(基本上只是手语的风格)不应该可供选择.例如，您不能使用常规"与聋/盲人的(视觉)手语.这是因为常规手语取决于能够看到的人.相反，人们会使用触觉 ASL".这是手语的手语版本.基本上，我想根据选择的客户端类型来限制模式列表.

为了开始我的关联，我正在 tlkClientClientType 和 tlkClientModality 表之间创建一个连接表.在这里，我将创建正确的允许的客户端类型和模式对.此外，实际上并不存在必要的结域".来证明这种关系.我正在考虑制作一个虚拟场"以此来证明这种关系的合理性.

稍后，在客户端的表单中，我将编辑模式组合框上的行源查询，以依赖于在客户端类型框中选择的选项.这将通过检查联结表中的哪些记录与客户端类型组合框中的选择相匹配来实现.

我在正确的轨道上吗?在两个查找表之间创建一个连接表似乎很奇怪.有什么问题吗?

注意——我想远离 VBA，但我必须编写宏来实现这些目标.任何想法将不胜感激.

下面给出了关系的照片.

您想从客户端组合框设置模式组合框内容.这是视图的问题而不是模型的问题，因此我们更改了视图而不是数据的组织.关键是从客户端组合框的afterupdate事件中设置模态组合框的记录源.首先显示结果:如果客户是聋子，我们会得到:

如果客户是盲人或盲聋哑人，我们会得到:

私有子 cmbClient_AfterUpdate()Dim RegularASL 为整数:RegularASL = 1将 TactileASL 调暗为整数:TactileASL = 2Dim ClientID As Integer: ClientID = Me.cmbClient如果 ClientisBlindandDeaf(ClientID) 那么cmbModality.RowSource = "SELECT * FROM tlkClientModality WHERE ClientModalityID = "&触觉手语ElseIf isClientBlind(ClientID) ThencmbModality.RowSource = "SELECT * FROM tlkClientModality WHERE ClientModalityID = "&触觉手语别的cmbModality.RowSource = "SELECT * FROM tlkClientModality WHERE ClientModalityID = "&常规手语万一cmbModality.Requery '重新加载 cmbmodality 数据Me.Refresh 'repaint cmbmodality结束子公共函数 isClientBlind(ClientID As Integer) As Boolean'去掉名字中的 * vba 无法解析 *Dim isblind: isblind = 2Dim ClientClientTypeID As Variant '允许空返回类型ClientClientTypeID = DLookup("ClientClientTypeID", "tlkClientClientType", "ClientID = " & ClientID & " AND ClientTypeID = " & isblind)如果 IsNull(ClientClientTypeID) 那么isClientBlind = False别的isClientBlind = 真万一结束函数公共函数 isClientDeaf(ClientID As Integer) As BooleanDim isdeaf: isdeaf = 1Dim ClientClientTypeID As Variant '允许空返回类型ClientClientTypeID = DLookup("ClientClientTypeID", "tlkClientClientType", "ClientID = " & ClientID & " AND ClientTypeID = " & isdeaf)如果 IsNull(ClientClientTypeID) 那么isClientDeaf = False别的isClientDeaf = 真万一结束函数公共函数 ClientisBlindandDeaf(ClientID As Integer) As Boolean如果 isClientBlind(ClientID) 和 isClientDeaf(ClientID) 那么ClientisBlindandDeaf = True别的ClientisBlindandDeaf = False万一结束函数

注意:cmbModality 的设置就像它绑定到整个 tlkModality 表一样，然后使用记录源更改对其进行过滤.

I am making a database for a freelance sign language interpreter. I have a subject table tblClient which holds information regarding the freelancer's clients. There is a lookup table tlkClientClientType showing the various types of clients (categorized on condition -- deaf, deaf/blind, etc). There is also a table called tlkClientModality. In this table are the different types of sign language offered by the interpreter. You can pick the client's preferred modality from the Client table.

Now the tricky part. There are certain modalities (basically just flavors of sign language) that should not be available to pick if the client is a certain type. For example, you cannot use "regular" (visual) sign language with someone who is deaf/blind. This is because regular sign language depends on the person being able to see. Instead, one would use "Tactile ASL" which is a hand-over-hand version of sign language. Basically, I want to limit the modality list based on the client type picked.

To start off my associations, I am making a junction table between the tlkClientClientType and tlkClientModality tables. Here, I will create the correct allowable pairs of client types and modalities. In addition, there is not really a necessary "junction field" to justify this relationship. I am considering making a "dummy field" as a way to still justify such a relationship.

Later, in a form for the client, I will edit the row source query on the modality combo box to be dependent on the choice selected in the client type box. This would be accomplished by checking what records in the junction table match the choice in the client type combo box.

Am I on the right track here? Making a junction table between two lookup tables seems weird. Is there anything wrong with it?

Note -- I would like to stay away from VBA, but I am up to writing macros to accomplish these goals. Any thoughts would be appreciated.

A photo of the relationships is given below.

Relationshipsphoto

解决方案

Keep your tables in whatever normal form works for your business case. Your choice just changes how the information is stored which means you have to change how you write your queries as the information may be located in a different place. I chose a many to many relationship between client and clienttype. You want to set the modality combobox contents from the client combobox. This is a problem of the view not the model hence we change the view and not the organization of the data. the key is to set the recordsource of the modality combobox from the afterupdate event of the client combobox. Showing the results first: if the client is deaf we get:

if the client is blind or blind and deaf we get:

Private Sub cmbClient_AfterUpdate()
Dim RegularASL As Integer: RegularASL = 1
Dim TactileASL As Integer: TactileASL = 2
Dim ClientID As Integer: ClientID = Me.cmbClient
If ClientisBlindandDeaf(ClientID) Then
cmbModality.RowSource = "SELECT * FROM tlkClientModality WHERE ClientModalityID = " & TactileASL
ElseIf isClientBlind(ClientID) Then
cmbModality.RowSource = "SELECT * FROM tlkClientModality WHERE ClientModalityID = " & TactileASL
Else
cmbModality.RowSource = "SELECT * FROM tlkClientModality WHERE ClientModalityID = " & RegularASL
End If
cmbModality.Requery 'reload cmbmodality data
Me.Refresh 'repaint cmbmodality
End Sub
Public Function isClientBlind(ClientID As Integer) As Boolean
'Get rid of * in names vba can't parse the *
Dim isblind: isblind = 2
Dim ClientClientTypeID As Variant 'allows null return type
ClientClientTypeID = DLookup("ClientClientTypeID", "tlkClientClientType", "ClientID = " & ClientID & " AND ClientTypeID = " & isblind)
If IsNull(ClientClientTypeID) Then
isClientBlind = False
Else
isClientBlind = True
End If
End Function
Public Function isClientDeaf(ClientID As Integer) As Boolean
Dim isdeaf: isdeaf = 1
Dim ClientClientTypeID As Variant 'allows null return type
ClientClientTypeID = DLookup("ClientClientTypeID", "tlkClientClientType", "ClientID = " & ClientID & " AND ClientTypeID = " & isdeaf)
If IsNull(ClientClientTypeID) Then
isClientDeaf = False
Else
isClientDeaf = True
End If
End Function

Public Function ClientisBlindandDeaf(ClientID As Integer) As Boolean
If isClientBlind(ClientID) And isClientDeaf(ClientID) Then
ClientisBlindandDeaf = True
Else
ClientisBlindandDeaf = False
End If
End Function

note: cmbModality is set up just like it is bound to the entire tlkModality table then the record source changes are used to filter it in effect.

这篇关于类别之间的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！