473,883 Members | 1,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

query by field heading

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
Nov 12 '05 #1
13 3541
John,

Look at QueryDef in the Help file.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"John young" <ja*****@arach. net.au> wrote in message
news:82******** *************** ***@posting.goo gle.com...
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

Nov 12 '05 #2
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 "lstFieldLi st"
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.Dodg e
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 "lstFieldLi st" 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 "sbfVolunte ers"

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(Cance l As Integer)
DoCmd.Maximize
sRequerySubform
End Sub

Private Sub lstFieldList_Af terUpdate()
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.ItemsSelect ed.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.ItemsSelect ed.Count > 0 Then

For Each varItm In ctl.ItemsSelect ed

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

Next varItm

End If

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

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

Me.sbfVolunteer s.Form.RecordSo urce = 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.N et 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.goo gle.com...
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

Nov 12 '05 #3
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 ...
Nov 12 '05 #4
Hi John,

Easy fix...

We'll just use the same "MySQL" string as the FilterName for the report.
This is what I like to call the WYSIWYG approach to reporting. :)
[What You See (displayed in the subform) Is What You Get] on the report.

1.) Move the "Dim MySQL As String" declaration to the form's declaration
section (like this) so that this string variable is available to all
procedures on this form.
*************** *************** *******
Option Compare Database
Option Explicit
Dim MySQL As String
*************** *************** *******

2.) I'm assuming that you have already designed a report, and have named it
"rptVolunteers" ...
If you haven't, go ahead and create it based directly on the "Volunteers "
table. When you open it from the DB window it will show ALL records, but
when you open it from the command button on your form it will show just the
filtered records that are being displayed in the subform.

3.) Create a command button called "cmdOpenRep ort" using the button wizard
.... then just add " , MySQL " in the optional [filtername] parameter of the
OpenReport Method. (like this)
*************** *************** *******
Private Sub cmdOpenReport_C lick()
On Error GoTo Err_cmdOpenRepo rt_Click

Dim stDocName As String

stDocName = "rptVolunte ers"
DoCmd.OpenRepor t stDocName, acPreview, MySQL ' Added the " , MySQL "
here

Exit_cmdOpenRep ort_Click:
Exit Sub

Err_cmdOpenRepo rt_Click:
MsgBox Err.Description
Resume Exit_cmdOpenRep ort_Click

End Sub
*************** *************** *******

--
HTH,
Don
=============== ==============
Use My*****@Telus.N et 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.goo gle.com...
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 ...

Nov 12 '05 #5
Don
thanks for your reply .... works well and I can use it ... but
unfortunatly not what i am after .....
i really want to produce report that has only selected field
information from the selection list box... with the field selected as
the 'variable' to display as heading on report and 'true' data below
....... so as you select a diffent category from list box it displays
only that data in report...

sorry for the confusion .....
Nov 12 '05 #6
Hi John,

If I'm finally "getting this" ... you only want the colmn headings in the
report to be visible only if they were the fields that you selected in
lstFieldNames? That sounds a little tougher, but probably still not
impossible. Is it important that they be positioned exactly where you want
them on this report? I guess that could be done too using each label and
fields .Left property, and then repositioning it later?

I'm thinking of building an array of selected fields, passing that to the
OpenArgs, then setting the report column's .Visible property to true for all
fields in the array. The other fields not in the array would get their
..visible property set to False. I'm thinking that the report's OnFormat
event is where this test should run.

I don't have time to mess with this today, maybe you could give that a try,
or at least confirm that I'm on the right track now?

Don

"John young" <ja*****@arach. net.au> wrote in message
news:82******** *************** ***@posting.goo gle.com...
Don
thanks for your reply .... works well and I can use it ... but
unfortunatly not what i am after .....
i really want to produce report that has only selected field
information from the selection list box... with the field selected as
the 'variable' to display as heading on report and 'true' data below
...... so as you select a diffent category from list box it displays
only that data in report...

sorry for the confusion .....

Nov 12 '05 #7
Hi Chuck,

That sounds like a cool tip, if I understand the concept correctly.

We're talking about basically assigning an "unbound" control's Control
Source property to whatever value I carry forward using the OpenArgs,
correct?
I imagine that you should be able to also assign the label's caption
property somehow too?

Would you mind explaining this a little more?

Regards,
Don
Chuck Grimsby <c.*******@worl dnet.att.net.in valid> wrote in message
news:e7******** *************** *********@4ax.c om...

Hint: Use the Ordinal field position rather then the field's name as
the control's recordsource

Example: recordset.Field s(0)
On Wed, 05 May 2004 14:00:15 GMT, "Don Leverton"
<le************ ****@telusplane t.net> wrote:
If I'm finally "getting this" ... you only want the colmn headings in the
report to be visible only if they were the fields that you selected in
lstFieldName s? That sounds a little tougher, but probably still not
impossible. Is it important that they be positioned exactly where you wantthem on this report? I guess that could be done too using each label and
fields .Left property, and then repositioning it later?
I'm thinking of building an array of selected fields, passing that to the
OpenArgs, then setting the report column's .Visible property to true for allfields in the array. The other fields not in the array would get their
.visible property set to False. I'm thinking that the report's OnFormat
event is where this test should run.
I don't have time to mess with this today, maybe you could give that a try,or at least confirm that I'm on the right track now?


Nov 12 '05 #8
Hi Chuck,

Disregard my stupid question about the OpenArgs thing <blush>
It appears that Reports don't HAVE an OpenArgs ... aheh, heh, umm, ya,
heh-heh.

I'm investigating the "CreateReportCo ntrol" Function as I type this. It
looks kind of interesting...
Don

"Don Leverton" <My*****@Telus. Net> wrote in message
news:Hffmc.2872 $uN4.1045@clgrp s12...
Hi Chuck,

That sounds like a cool tip, if I understand the concept correctly.

We're talking about basically assigning an "unbound" control's Control
Source property to whatever value I carry forward using the OpenArgs,
correct?
I imagine that you should be able to also assign the label's caption
property somehow too?

Would you mind explaining this a little more?

Regards,
Don
Chuck Grimsby <c.*******@worl dnet.att.net.in valid> wrote in message
news:e7******** *************** *********@4ax.c om...

Hint: Use the Ordinal field position rather then the field's name as
the control's recordsource

Example: recordset.Field s(0)
On Wed, 05 May 2004 14:00:15 GMT, "Don Leverton"
<le************ ****@telusplane t.net> wrote:
If I'm finally "getting this" ... you only want the colmn headings in thereport to be visible only if they were the fields that you selected in
lstFieldName s? That sounds a little tougher, but probably still not
impossible. Is it important that they be positioned exactly where you wantthem on this report? I guess that could be done too using each label andfields .Left property, and then repositioning it later?
I'm thinking of building an array of selected fields, passing that to theOpenArgs, then setting the report column's .Visible property to true
for
allfields in the array. The other fields not in the array would get their
.visible property set to False. I'm thinking that the report's OnFormat
event is where this test should run.
I don't have time to mess with this today, maybe you could give that a try,or at least confirm that I'm on the right track now?


Nov 12 '05 #9
Don, Chuck
looks like i have created a headache .. and hoped my explantion
of what i am trying was put forward plainly....
to try and make it clearer....
if i create a standard query from volunteer table with only two
fields, say "Mem No" and "Magazine"
and set the criteria for selection as showing 'Mem No' where
'Magazine' is 'True' ( all category fields are yes/no or true/false )
I get a list of Mem No's that meet this requirement.
If I change the field from "magazine" to "Parts", I get a list of
member no's that meet this requirement.. and so it goes on for each of
the 'categories' in the table .......
This can make for a large number of stand alone queries and
"reports" that have to be pre made ...
What i am looking for is to be able to select the category from a
list of field headings (categories) as previosly shown and for the
query / report to reflect that info.... this basically gives me one
query and one report structure to display those members who meet the
criteria for any given category (field heading)...
note: this query would be liked to 'members' table(by mem
no)to display name and contact as well..

hope this clears the fog ...
cheers john
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2825
by: Tom | last post by:
I have a report based on a crosstab query that shows monthly automobile sales. The report is grouped on Make so that it shows the models under each Make. I want to now get the subtotals by Make and then the total for all for each month. I find that the control source for the textboxes in the detail section have a date value such as Jan_03 rather than dollars but when I run the report dollars appears in the report. Obviously I can't sum up...
4
5337
by: pw | last post by:
Hi, I have month names (coming from a field in a table) as the column heading in an Access 97 crosstab query. It is being sorted alphabetically. This will not do. The only way that I know to get around it is to use month numbers instead, but I'd rather have the names appear. Any ideas?
3
579
by: John young | last post by:
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
3
3310
by: MLH | last post by:
Am repeating question with different subject heading, perhaps stating more clearly my problem... I have an A97 query (qryVehiclesNowners2) that has a table field in it named . Depending on the selections made in a number of criteria choices on a form, a field on the form will have string values in it like... Between #12/1/2004# And #12/31/2004# - or - >= #6/10/2005# - or -
0
2026
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey results, survey questions and survey response choices. Survey response choices has over 800 choices types. Survey results capture results of all the surveys and has questions, respondent, response choice, response, responsechoice order which looks...
0
2175
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey results, survey questions and survey response choices. Survey response choices has over 800 choices types. Survey results capture results of all the surveys and has questions, respondent, response choice, response, responsechoice order which
2
2941
by: Nenad Markovic | last post by:
Hi everybody, When executing a Crosstab Query I see only rows (defined in a row heading) that have values (defined in value field) in at least one column (defined as column headings). How can I make a Crosstab Query that shows all rows, regardless they have values in column headings. For instance, the result that I get now is like this
2
2180
by: x | last post by:
hi i have made a crosstab query in which row heading is "date", colum heading is "aircraft type" and the value to be summed up is "1-10 row totals". i want to create a simple query which should just give me the total value of "1-10 row totals" field by defining a certain criteria. now when i write the following in the criteria field Between and i get an error message saying "The microsoft jet database engine doesn't recognize as a...
6
13242
by: lisacrowe | last post by:
I have a simple database recording complaints. A crosstab query is based on a query which returns resolved complaints only. The crosstab has the field Complaint Type as a row heading and Outcome as a column heading. I want to be able to select complaints which were resolved in a particular date range (field is Date resolved). This field is in the original query but not the crosstab. How do I do it?
1
1347
by: GLEberts | last post by:
I have a crosstab query for 12 months of sales. It works well except is adds all the months sales together for every year. Does not seperate 2009 from 2008 and so on. It sums all the years together per month. Column 1 Field: Salesrep Table: tblJob Total: Group By Crosstab: Row Heading Column 2 Field: Expr1: Format(,"mmm")
0
9793
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10752
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10858
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9582
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7974
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5996
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4225
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.