创建动态的“复选框"在Access中查询最终用户 [英] Create a dynamic "checkbox" query in Access for end users

问题描述

用例场景

我有一个低收入居民的客户.这些居民可以用一堆复选框填写纸质表格.

I have a client with low-income residents. These residents can fill out a paper form with a bunch of check boxes.

其中一个部分用于教育"(GED，文凭，员工等)，另一部分用于技能"(暖通空调，水暖等)

It has one section for "Education" (GED, diploma, associates, etc) and another for "Skills" (HVAC, plumbing, etc)

雇主与我的客户联系，例如，要求所有接受HVAC培训的居民持有GED.

Employers reach out to my client, and for example ask for all their HVAC trained residents with a GED.

现在，他们手动梳理纸质记录以寻找匹配项

Right now, they manually comb through paper records looking for a match

到目前为止的解决方案

我正在使用访问数据库对其进行设置.

I am setting them up with an access database.

我们在短字段中输入姓名，单元格，电子邮件，地址等.

We have short fields for name, cell, email, address, etc.

然后我们在纸质表格上显示是/否框，以显示不同技能.

We then have yes/no boxes for the different skills that appear on the paper form.

我已经建立了表格和表格，客户员工一旦收到纸质副本，便可以轻松地填写表格.

I have built the table and a form that the clients employees can easily fill out once they receive the paper copy.

我的问题我需要您的帮助

我希望有一个动态查询，提示最终用户(我的客户的雇员)简单地选中某些复选框并吐出居民的个人信息.

I would like to have a dynamic query that prompts the end user (my clients' employees) to simply check some boxes and spit out the Personal Information of the resident.

例如，他们可以在"HVAC"上打钩，然后在"GED"上打钩，单击搜索按钮以对是/否"框启动查询，并吐出与他们的技能和教育相匹配的居民的个人信息.检查.

For example, they can tick "HVAC" and then tick "GED", hit a search button that starts the query against the yes/no boxes, and spits out the personal information of the residents that match the skills and education they checked.

我为每种技能构建了单独的预制静态查询，但这很笨拙，并且不是那么用户友好.

I've built individual pre-made static queries for each individual skill, but it's clunky and isn't that user friendly.

在访问方面，我非常缺乏经验，如果您拥有涵盖此确切场景的培训材料(youtube视频，操作方法网站)，我可以确定这一点.我认为我的问题是，我对访问术语不够了解，无法正确搜索我要查找的内容.

I am very inexperienced when it comes to access, if you have training material (youtube video, how-to site) that covers this exact scenario, I can figure out with just that. I think my issue is, I don't know access terminology enough to properly search for what I'm wanting to find.

推荐答案

对于这种简单的情况，您只希望按yes/no列进行过滤，则不必使用VBA.不过，它更灵活(也许更有效).

For this simple case, where you only want to filter by yes/no columns, you don't have to use VBA. It's more flexible (and perhaps efficient) though.

创建一个搜索表单(我们称其为 frmSearch )，其中包含所有技能( chkHVAC ， chkGED 等)的未绑定复选框.

Create a search form (let's call it frmSearch), with unbound check boxes for all skills (chkHVAC, chkGED etc.)

然后，您可以使用布尔逻辑使用涵盖所有技能的单个查询:

Then you can use a single query that covers all skills, using boolean logic:

• 如果未选中此复选框( chkHVAC = False )，则不会过滤此技能(无论字段是true还是false，都会返回记录.)
• 如果选中，则仅返回字段= True的记录.

• If the check box is not checked (chkHVAC = False), this skill is not filtered (records are returned, whether the field is true or false).
• If it is checked, only records are returned that have the field = True.

赞. [HVAC] 和 [GED] 是表格列.

SELECT *
FROM myTable
WHERE (Forms!frmSearch!chkHVAC = False OR [HVAC] = True)
  AND (Forms!frmSearch!chkGED  = False OR [GED] = True)
  AND ... etc.

您可以在搜索表单上放置一个按钮，以打开查询(如果已经打开，则可以将其关闭):

You can put a button on the search form, that opens the query (and perhaps closes it if it's already open):

DoCmd.Close acQuery, "myQuery"
DoCmd.OpenQuery "myQuery

这篇关于创建动态的“复选框"在Access中查询最终用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！