在Microsoft Access中创建只能由某些字段搜索的数据库 [英] Creating a database in Microsoft Access that is searchable only by certain fields

问题描述

您将如何在Microsoft Access中创建一个只能由某些字段搜索并且仅由表单上的几个（必要）文本框和复选框控制的数据库，因此易于使用-无需困难的查询？

How would you create a database in Microsoft Access that is searchable only by certain fields and controlled by only a few (necessary) text boxes and check boxes on a form so it is easy to use - no difficult queries?

示例：
表单上有多个文本框和几个对应的复选框，并且选中了该文本框旁边的复选框后，该文本框即被启用然后您可以按输入的文本框中的内容进行搜索

Example: You have several text boxes and several corresponding check boxes on a form, and when the check box next to the text box is checked, the text box is enabled and you can then search by what is entered into said text box

（实际上，我已经知道这一点，只是在玩stackoverflow危险游戏时，我问一个问题，我知道答案就是来增加世界各地的编码知识！大约5分钟后回答）

(Actually I already know this, just playing stackoverflow jeopardy, where I ask a question I know the answer just to increase the world's coding knowledge! answer coming in about 5 mins)

推荐答案

这实际上是一个很大的话题，充满了各种潜在的问题。关于Access的大多数中级到高级书籍都将在某种程度上讨论按表格查询，您可以在其中拥有一个未绑定的表格，该表格允许用户选择某些条件，并且在执行时会编写即时SQL以返回匹配数据。

This is actually a pretty large topic, and fraught with all kinds of potential problems. Most intermediate to advanced books on Access will have some kind of section discussing "Query by Form," where you have an unbound form that allows the user to choose certain criteria, and that when executed, writes on-the-fly SQL to return the matching data.

在单一的单表数据结构中，这并不是一件容易的事，因为SQL的FROM子句取决于查询的表。

In anything but a flat, single-table data structure, this is not a trivial task because the FROM clause of the SQL is dependent on the tables queried in the WHERE clause.

我为客户创建的应用中一些QBF表单的示例：

A few examples of some QBF forms from apps I've created for clients:

1. 查询4个基础表


2. 查询平面单个表


3. 查询3个基础表


4. 查询6个基础表


5. 查询2个基础表

1. Querying 4 underlying tables
2. Querying a flat single table
3. Querying 3 underlying tables
4. Querying 6 underlying tables
5. Querying 2 underlying tables

第一个是由具有以下内容的类模块驱动的这些属性反映了以这种形式选择的标准，并具有编写FROM和WHERE子句的方法。这使得添加其他字段非常容易（只要这些字段不是来自已经包含的表之外的其他表）。

The first one is driven by a class module that has properties that reflect the criteria selected in this form, and that has methods that write the FROM and WHERE clauses. This makes it extremely easy to add other fields (as long as those fields don't come from tables other than the ones already included).

进程正在编写FROM子句，因为您必须具有适当的联接类型，并且仅包括SELECT子句或WHERE子句中的表。如果包含其他内容，则会大大降低查询速度（特别是如果有任何外部联接）。

The most complex part of the process is writing the FROM clause, as you have to have appropriate join types and include only the tables that are either in the SELECT clause or the WHERE clause. If you include anything else, you'll slow down your query a lot (especially if you have any outer joins).

但这是一个很大的主题，没有神奇的解决方案-相反，必须为每个特定的应用程序创建类似的东西。与用户进行全面测试也很重要，因为对于开发人员而言，您完全清楚和可以理解的内容通常对最终用户非常神秘。

But this is a big subject, and there is no magic bullet solution -- instead, something like this has to be created for each particular application. It's also important that you test it thoroughly with users, since what is completely clear and understandable to you, the developer, is often pretty darned mystifying to end users.

不仅适用于QBF的原则！

But that's a principle that doesn't just apply to QBF!

这篇关于在Microsoft Access中创建只能由某些字段搜索的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！