尝试在表单上创建条件名称列表 [英] Trying to create a conditional list of field names on a form

问题描述

我有一个旧数据库，用于跟踪员工参加培训活动的情况.每条记录都是一名员工，并遵循基本的名字/姓氏电话号码等...大约有150个字段代表过去的事件，每个字段带有是/否复选框，指示该员工是否参加了该事件.

I have an old database that is used to keep track of employee attendance at training events. Each record is an employee and following the basic first name/last name phone number ect... there are about 150 fields representing past events, each with a yes/no checkbox indicating whether that employee attended that event.

我有一张表格，可以轻松查看和编辑员工记录.我目前有大量的事件列表，其中的复选框与主表中的相应字段相关.

I have a form that allows employee records to be easily viewed and edited. I currently have a massive list of the events with checkboxes that are tied to the corresponding fields in the master table.

我需要创建当前正在查看的员工参加的事件的列表，以便显示为指定员工检查的所有字段名称.

I need to create a list of events that the employee currently being viewed has attended, so a display of all the field names that are checked for the specified employee.

我知道我正在使用的数据库的设计显然是不完善的，但是有什么办法可以做到这一点?

I'm aware that the design of the database I'm working with is obviously imperfect, but is there any way that I can do this?

提前谢谢！

推荐答案

您基本上需要使用

EmployeeID  EventID
----------  -------
         1  event1
         1  event3
         1  event7

在Access中取消透视"的最常见方法是对UNION ALL进行一系列单独的SELECT.但是，这仅适用于大约十二个UNION(此后Access会引发查询过于复杂"错误)，因此您必须将其分解为较小的事件"字段组，并将结果存储在临时字段中桌子.

The most common approach to "unpivot" in Access is to UNION ALL a series of individual SELECTs. However, that is only good for about a dozen UNIONs (after which Access throws a "query is too complex" error), so you'll have to break it up into smaller groups of "event" fields and store the results in a temporary table.

为避免一遍又一遍地执行该混乱的"unpivot"操作，您应该修复数据库结构以使该"unpivot"表永久存在，并摆脱另一个表中的〜150个字段.这是短期痛苦，长期收获"的经典案例.

To avoid having to do that messy "unpivoting" over and over again, you should just fix your database structure to make that "unpivoted" table permanent and get rid of the ~150 fields in the other table. It's a classic case of "short-term pain, long-term gain".

这篇关于尝试在表单上创建条件名称列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！