Solved

Multiple Fields in an Access Query

Posted on 2004-08-16
13
255 Views
Last Modified: 2012-06-22
I have a table where the data in two fields relate to the same thing.

For Example:

Name               Address               Suburb                    State Council 1          State Council 2
Phil                  Over-the-Hill         Far Away                Anderson                   Peterson
Michael             Lost Tree            Neverneverland        Clarke                       Louis
Gregory            Sherwood            Red Riding Hood       Phelps                      Anderson

In the above example, I would like to be able to have a query that allows me to show the records that contain 'Anderson'. Ultimately, I would like to have the option to enter in the search data, but first - the baby steps.

Thanks
0
Comment
Question by:mhpountney
  • 3
  • 3
  • 2
  • +2
13 Comments
 
LVL 5

Expert Comment

by:peterpuscas
ID: 11817184
select * from yourTable where  ([Name] = 'Anderson') or ([Address]='Anderson') or ([Suburb]='Anderson') or
([State Council 1]='Anderson') or ([State Council 2]='Anderson')

Peter
0
 
LVL 34

Expert Comment

by:flavo
ID: 11817199
select * from yourTable where  ([Name] = [Enter Your Serch]) or ([Address]=[Enter Your Serch]) or ([Suburb]=[Enter Your Serch]) or
([State Council 1]=[Enter Your Serch]) or ([State Council 2]=[Enter Your Serch])

Run it and you will get prompted.

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 11817203
my bad

select * from yourTable where ([Name] = [Enter Your Search]) or ([Address]=[Enter Your Search]) or ([Suburb]=[Enter Your Search]) or
([State Council 1]=[Enter Your Search]) or ([State Council 2]=[Enter Your Search]);
0
 

Author Comment

by:mhpountney
ID: 11817540
I only want to enter in the search request once, when it comes to the search.

ie: [enter your search] for State Council

will mean I can type in 'Anderson', and all 'State Council' records containing Anderson will show up.
0
 
LVL 34

Accepted Solution

by:
flavo earned 125 total points
ID: 11817561
the SQL i posted, you will only have to enter it once and it will sreach all fields.

If you only want the state council 's then use

select * from yourTable where ([State Council 1]=[Enter Your Search]) or ([State Council 2]=[Enter Your Search]);

If thats not what you want then explain exactly what you are after..

Dave
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Expert Comment

by:peterpuscas
ID: 11817588
Than you have to build the SQL Statement from the form's code,
in this question you asked for all the fields at the same time.

something like:

s = "Select * from yourTable where (0=0) " 
if len(textSearch1.value)>0 then
  s = s+"and (field1 = '"+textSearch1.value+"')"
end if

... and so on


Peter



0
 

Author Comment

by:mhpountney
ID: 11817609
I'll admit I'm a little slow, but I am not sure where the select*... should be placed in reference to my query.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11818108
You would get a SELECT * if you grab the * at the top of you field names and dragged it onto the fields section of the query designer.

SELECT * is in the actual SQL of the query. When you design a query one of the View options is SQL that allows you to see this.

* means: give me all of the fields in the table

Steve
0
 

Expert Comment

by:pmcelhany
ID: 11824652
Did you ever get this one figured out.  I might be able to help.
0
 
LVL 5

Expert Comment

by:peterpuscas
ID: 11826558
From Forms Tab:
  Create a form in design view
 
  put on the Form from toolbox:
         5 textBox
         1 button
         1 listbox
select these objects one by one and from View\Properties\ Other tab
set their names to
         txtName, txtAddress, txtSuburb, txtState1, txtState2
         cmdSearch
         lstData

Select the button, from properties, events tab, on the on Click event
put this code:

Private Sub cmdSearch_Click()
 
s = "Select * from Table1 where (0=0) "

If Len(txtName.Value) > 0 Then
  s = s + "and ([Name] = '" + txtName.Value + "')"
End If

If Len(txtAddress.Value) > 0 Then
  s = s + "and ([Address] = '" + txtAddress.Value + "')"
End If

If Len(txtSuburb.Value) > 0 Then
  s = s + "and ([Suburb] = '" + txtSuburb.Value + "')"
End If

If Len(txtState1.Value) > 0 Then
  s = s + "and ([State Council 1] = '" + txtState1.Value + "')"
End If

If Len(txtState2.Value) > 0 Then
  s = s + "and ([State Council 2] = '" + txtState2.Value + "')"
End If

lstData.RowSource = s
lstData.Requery

End Sub


Good luck,
Peter
0
 

Expert Comment

by:pmcelhany
ID: 11844062
Try this:
Create a form called: frm_Who
Put one control on the form called: Who
Create a select qry called: qry_who
Add all of the fields from your table to the query
On the first criteria line under StateCouncil1 add the line:
IIf(IsNull([forms]![frm_Who]![who]),[tbl_1]![Statecouncil1],[forms]![frm_Who]![Who])
On the second line under StateCouncil2 add the line:
IIf(IsNull([forms]![frm_Who]![who]),[tbl_1]![Statecouncil1],[forms]![frm_Who]![Who])

These two lines will act as an "Or" statement.

Go to your form and enter the name of a council member that you want to search for.
Be sure to hit enter to commit the name.

Go run your query and see if it works.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now