按字段标题查询 [英] query by field heading

问题描述

我一直在寻找问题的答案，并找到了这个

组，希望你能帮忙。


我一直在做数据库我已经为一个汽车俱乐部制作了

并且一直试图从桌子中选择一个查询，因为

下面描述的......


我有一张桌子（志愿者），有一个成员字段（memnumber）

和许多领域各种类别的领域是

是/否已形成


我想通过选择成员获得创建查询/报告

为true基于字段类别标题，选择一个

类别作为变量，从具有字段/类别

标题的查找表中，而不是创建一些独立的查询。

这些字段/类别将来可能会增加


这可以轻松完成还是涉及...... ???

我不是专家，但有合理数量的用户

知识


我正在使用Windows XP和Access 2002


我们将非常感激您的帮助

解决方案

John，


查看帮助文件中的QueryDef。


-

PC数据表

您的资源以获取Access，Excel和Word的帮助应用程序
re******@pcdatasheet.com
www.pcdatasheet.com

" John young" < JA ***** @ arach.net.au>在消息中写道

news：82 ************************** @ posting.google.c om ...

我一直在寻找问题的答案并找到了这个
组，并希望你能提供帮助。

我一直在做一个数据库我我已经为一个汽车俱乐部做了
并且一直在努力提出一个从桌子中选择的查询，如下所示......

我有一张桌子（志愿者）有一个成员字段（memnumber）
和许多领域的各种类别，是
是/否格式

我想得到一个创建查询/通过选择真实的成员报告
基于字段类别标题，通过从具有字段/类别标题的查找表中选择
类别作为变量，而不是创建一些独立查询。
这些字段/类别可能在未来增加

这可以轻松完成或涉及...... ???
我不是专家，但有合理数量的用户
我正在使用Windows XP和Access 2002

我们将非常感谢您的帮助

< blockquote>嗨John，


我不清楚决定是否在结果中包含一个

成员的标准，所以承担和我以及我的假设一样，我尝试使用
来尽可能灵活。


在这种情况下让我们说你希望成员显示在哪里* *

字段*或*许多字段*中的一个*包含真字段*值。 （即，字段名称

Ford，或Chev，或Dodge包含True值。）


这可以通过使用多选列表框来最好地完成（恕我直言）

指定标准。


1.）设计一个带有列表的未绑定主表单包含字段的-box控件

列表。


使用以下方法创建此列表框：

a。）选择工具栏中的列表框工具，并将其放在

表格上。

b。）当列表框向导启动时...点击[取消]按钮......

我们想做一些向导不打算做的事情。

c。）打开列表框的属性表（如果没有已打开）。

d。）名称：让我们称之为lstFieldList

RowSource类型：保留为表/查询现在，但我们将在

a分钟内更改此内容。

多选：选择扩展

e。）在行源字段，选择表格（志愿者）

包含是/否字段。

f。）选择表格后，单击椭圆（...）调用

查询生成器，并回答是。当提示时。

g。）当设计视图时出现QBE网格，选择您希望在字段列表中显示的是/否

字段。

h。）如果您愿意，可以确认通过打开查询，选择字段包含

是或否值在数据表视图中。

i。）现在从设计或数据表切换查看SQL查看并插入

此字符串 - Bogus As''（All）''， - 紧跟在单词SELECT之后在你的SQL

声明中。


它现在应该是这样的：


SELECT Bogus As'' （全部）''，Volunteers.Ford，Volunteers.Chev，Volunteers.Dodge

来自志愿者;


注意：添加''的想法（'所有）''到字段列表是这样我们可以

决定显示表中的所有记录，而不是按标准显示。


j。）关闭QBE网格。当提示将更改保存到SQL

语句并更新属性...时，请说是

k。）现在更改列表框'将RowSource类型设置为字段列表

l。）选择表单视图，然后查看lstFieldList。列表框。它应该

现在包含''（全部）''，然后是所需字段的列表。


2.）插入一个绑定的子窗体控件（再次基于Volunteer表）使用子窗体工具/向导来选择你希望在结果中显示的所需字段。让这个子表单命名为sbfVolunteers


3.）看看子表单是如何基于志愿者表的，默认情况下将显示所有记录

。我们要将子表单的

记录源更改为我们将用VBA代码构建的SQL语句：

********* ***************************************** ********* **

Private Sub Form_Open（取消为整数）

DoCmd.Maximize

sRequerySubform

End Sub


Private Sub lstFieldList_AfterUpdate（）

sRequerySubform

End Sub


Public Sub sRequerySubform（ ）

''我想在这里做的是为子表单构建RecordSource

''ON-THE-FLY ...以允许变量WHERE语句。

''以便让用户查看：

''1。）所有记录

''2.）过滤一个只有字段

''3.）由多个字段过滤


Dim ctl As Control

设置ctl = Me.lstFieldList


Dim Msg As String

将MySQL变为字符串


Dim CR As String

CR = vbCrLf


Dim varItm As Variant

Dim whr As String


''构建SQL的SELECT部分声明

MySQL =""

MySQL = MySQL& SELECT Volunteers。*

MySQL = MySQL& "来自志愿者"


''添加安全阻止用户组合全部的步骤任何

其他标准。

如果ctl.ItemsSelected.Count> 1和ctl.Selected（0）= True然后

Msg =""

Msg = Msg& 你不能选择''（全部）''和' &安培; CR

Msg = Msg& 任何其他标准。

MsgBox（消息）


ctl.Selected（0）= False

结束如果


''构建WHERE部分

whr ="" ''初始化变量

如果ctl.ItemsSelected.Count> 0然后


每个变量在ctl.ItemsSelected


如果Len（whr）> 0然后

whr = whr& "或 &安培; ctl.ItemData（varItm）& " = True"

Else

whr = whr& ctl.ItemData（varItm）& " = True"

结束如果


下一个varItm


结束如果

如果Len（whr）> 0然后

MySQL = MySQL& WHERE（& whr&"）"

结束如果


MySQL = MySQL&英寸;

''Debug.Print MySQL


Me.sbfVolunteers.Form.RecordSource = MySQL

设置ctl = Nothing


结束次级

***************************** ********************* **

实施上述所有措施后，您应该可以选择Chev。并且

拥有所有Chev = True显示的记录。

按[Ctrl]并单击福特按钮。 ...然后子窗体应显示所有

Chev = True和Ford = True记录等等！


这种方法的优点在于你可以添加一个字段Volvo。 （呀，

吧！）稍后。您需要做的就是将其添加到

组合框中的字段列表中，修改上面的1.g.）中的列表框。

-

HTH，

Don

======================== =====

使用 My*****@Telus.Net 进行电子操作邮件

免责声明：

Professional PartsPerson

业余数据库程序员{：o）


我'这是一个Access97用户，所以所有发布的代码

样本也是基于Access97的

除非另有说明。


Do Until SinksIn = True

文件/保存，<书桌抽屉里的手指敲击>

循环


====== ==========================


" John young" < JA ***** @ arach.net.au>在消息中写道

news：82 ************************** @ posting.google.c om ...

我一直在寻找问题的答案并找到了这个
组，并希望你能提供帮助。

我一直在做一个数据库我我已经为一个汽车俱乐部做了
并且一直在努力提出一个从桌子中选择的查询，如下所示......

我有一张桌子（志愿者）有一个成员字段（memnumber）
和许多领域的各种类别，是
是/否格式

我想得到一个创建查询/通过选择真实的成员报告
基于字段类别标题，通过从具有字段/类别标题的查找表中选择
类别作为变量，而不是创建一些独立查询。
这些字段/类别可能在未来增加

这可以轻松完成或涉及...... ???
我不是专家，但有合理数量的用户
我正在使用Windows XP和Access 2002

我们将非常感谢您的帮助

< blockquote> Don

我刚刚收到您的回复，我的查询已经完成了

实现了它，并在您指示时显示..


我的问题可能在我的措辞中不完整，因为我想要做的是创建一个查询/报告给我一个清单

/所有成员的打印输出（志愿者）被列为真实的对于

所选字段名称类别...例如杂志等。 ，交换满足，

" parts"等....


查询将引用会员号码，姓名，电话号码，电子邮件

来自会员表及相关信息"真"来自列表框中选择的

字段的标准...

希望这更清楚...

I have been looking for an answer to a problem and have found this
group and hope you can assist .

I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..

I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated

I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future

Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge

I am using Windows XP and Access 2002

Your help would be gratefully accepted

解决方案

John,

Look at QueryDef in the Help file.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...

I have been looking for an answer to a problem and have found this
group and hope you can assist .

I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..

I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated

I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future

Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge

I am using Windows XP and Access 2002

Your help would be gratefully accepted

Hi John,

I''m not real clear on the criteria that decides whether or not to include a
member in the results, so bear with me and my assumptions, while I attempt
to make this as flexible as possible.

In this situation let''s say you want members displayed where either *one
field* OR *one of many fields* contains a "True " value. (ie. field names
"Ford", or "Chev", or "Dodge" contain a "True" value.)

This can be accomplished best (IMHO) by using a multi-select listbox to
specify criteria.

1.) Design an unbound main form with a list-box control containing a field
list.

Create this list-box using the following method:
a.) Choose the list-box tool from the toolbar, and place it on your
form.
b.) When the list-box wizard starts up ... hit the [Cancel] button ...
we want to do something that the wizard is not designed to do.
c.) Open the property sheet for the list-box (if not already open).
d.) Name: Lets call it "lstFieldList"
RowSource Type: Leave as "Table/Query" for now, but we''ll change this in
a minute.
Multi Select: Choose "Extended"
e.) In the "Row Source" field, select the table (Volunteer) that
contains the Yes/No Fields.
f.) After selecting the table, click on the ellipse (...) to invoke the
Query Builder, and answer "Yes" when prompted.
g.) When the "Design View" QBE grid appears, select JUST the Yes/No
fields that you want to be displayed in the field list.
h.) If you like, you can confirm that the selected fields DO contain
either Yes or No values by opening the "query" in datasheet view.
i.) Now switch from "Design or "Datasheet" view to "SQL" View and insert
this string -- Bogus As ''(All)'', -- just after the word "SELECT" in your SQL
statement.

It should now look something like this:

SELECT Bogus As ''(All)'', Volunteers.Ford, Volunteers.Chev, Volunteers.Dodge
FROM Volunteers;

Note: The idea of adding an ''(All)'' to the field list is so that we can
decide to show all records in the table rather than JUST by criteria.

j.) Close the QBE grid. When prompted to "save the changes to the SQL
statement and update the property...", say Yes
k.) Now change the list-box''s RowSource Type to "Field List"
l.) Select Form view, and check out the "lstFieldList" list-box. It should
now contain ''(All)'', followed by a list of the desired fields.

2.) Insert a bound subform control (again based on Volunteer table) using
the subform tool / wizard to select the desired fields that you''d like to
display in your results. Lets name this subform "sbfVolunteers"

3.) Seeing as how the subform is based on the Volunteers table, all records
will be displayed by default. We''re going to change the subform''s
recordsource to an SQL statement that we will build in VBA code:
************************************************** ***********
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
sRequerySubform
End Sub

Private Sub lstFieldList_AfterUpdate()
sRequerySubform
End Sub

Public Sub sRequerySubform()
''What I want to do here is build the RecordSource for the subform
''ON-THE-FLY...to allow for a variable WHERE statement.
''in order to alow the user to view:
'' 1.) ALL records
'' 2.) filtered by one field only
'' 3.) filtered by multiple fields

Dim ctl As Control
Set ctl = Me.lstFieldList

Dim Msg As String
Dim MySQL As String

Dim CR As String
CR = vbCrLf

Dim varItm As Variant
Dim whr As String

''Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT Volunteers.*"
MySQL = MySQL & " FROM Volunteers "

''Add a "safety" step which prevents the user from combining "All" with any
other criteria.
If ctl.ItemsSelected.Count > 1 And ctl.Selected(0) = True Then
Msg = ""
Msg = Msg & "You cannot select ''(All)'' along with " & CR
Msg = Msg & "any other criteria."
MsgBox (Msg)

ctl.Selected(0) = False
End If

''Build the WHERE portion
whr = "" ''initialize variable
If ctl.ItemsSelected.Count > 0 Then

For Each varItm In ctl.ItemsSelected

If Len(whr) > 0 Then
whr = whr & " OR " & ctl.ItemData(varItm) & "= True"
Else
whr = whr & ctl.ItemData(varItm) & "= True"
End If

Next varItm

End If

If Len(whr) > 0 Then
MySQL = MySQL & "WHERE (" & whr & ")"
End If

MySQL = MySQL & "; "
''Debug.Print MySQL

Me.sbfVolunteers.Form.RecordSource = MySQL
Set ctl = Nothing

End Sub
************************************************** **
After implementing all of the above, you should be able to select "Chev" and
have all of the "Chev = True" records displayed.
Press [Ctrl] and click on "Ford" ... the subform should then display all of
the "Chev = True" and "Ford=True" records, and so on!

The beauty of this approach is that you could add a field "Volvo" (ya,
right!) later on. All that you need do to add this to the field list in the
combo-box is to modify the listbox as in 1.g.) above.
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I''m an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...

I have been looking for an answer to a problem and have found this
group and hope you can assist .

I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..

I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated

I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future

Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge

I am using Windows XP and Access 2002

Your help would be gratefully accepted

Don
I have just received your reply top my query and have
implemented it and it displays as you indicate ..

My Question may have been incomplete in my wording as what i
want to do is to create a query / report that gives me a list
/printout of all members (volunteers) who are listed as "True" for
the selected field name categorys ... eg "magazine" , "swap meet",
"parts" etc .... etc

The query will have reference to member number,name, phone no., email
from a members table along with the relevant "true" criteria from the
field selected by list box...
Hope this is clearer ...

这篇关于按字段标题查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！