I am trying to setup a search facility, such that anything selected in any
of 5 comboboxes is used as criteria unless null or "". I only want to get
back results where ALL selections are in a table.
The tables are similar to this
1.
UserID Name LName
2.
ColourID ColourName
3.
UserID
ColourID
I can easily do it with an OR style - so that if I selected Blue Red I get
anybody who has either of these colours in table 3. But I can't get it so
that it returns UserID's from only the people who have all colours in the
search.
Can anyone point me in the right direction? Haved searched, read FAQ's
etc - even helping with keywords to continue searching would be appreciated
(is there a name for this type of search?)
Thanks in advance... 5 2008
1) Create a crosstab query to give you an alternate view of the data in
Table 3
TRANSFORM First(T2.ColorN ame) AS Colors
SELECT T1.UserID, T1.UserName
FROM T2 INNER JOIN (T1 INNER JOIN T3 ON T1.UserID = T3.UserID) ON T2.ColorID
= T3.ColorID
GROUP BY T1.UserID, T1.UserName
PIVOT T2.Colorid;
Call this query Q_XTAB
Note: this query will show the UserID and Name as the Row Headings, the
ColorIDs as the Column Headings, and the ColorName as the data
2) Bind your comboBoxes to the ColorID and hide it (if you want to - set
width to 0"). If you don't want to put the ColorID in the comboBoxes,
you'll need to change the query to show the ColorName as the ColumnHeadings.
3) Build a dynamic query that processes each comboBox and executes against
Q_XTAB
Public Sub btnSearchBoxes_ Click()
'CONFIRM AT LEAST ONE COMBO BOX IS SELECTED
if isnull(me.combo 1) and isnull(me.combo 2) and isnull(me.combo 3) and
isnull(me.combo 4) and isnull(me.combo 5) then
msgbox "Choose at least one color"
exit sub
endif
'BUILD THE DYNAMIC QUERY
dim cSQL as string
cSQL = SELECT UserID, UserName
cSQL = cSQL & "FROM Q_XTAB "
cSQL = cSQL & "WHERE UserID IS NOT NULL AND "
IF not IsNull(me.combo 1) then cSQL = cSQL & "AND " & me.combo1 & " IS NOT
NULL "
IF not IsNull(me.combo 2) then cSQL = cSQL & "AND " & me.combo2 & " IS NOT
NULL "
IF not IsNull(me.combo 3) then cSQL = cSQL & "AND " & me.combo3 & " IS NOT
NULL "
IF not IsNull(me.combo 4) then cSQL = cSQL & "AND " & me.combo4 & " IS NOT
NULL "
IF not IsNull(me.combo 5) then cSQL = cSQL & "AND " & me.combo5 & " IS NOT
NULL "
End Sub
That should do it.
"T_2k" <ma*****@tmjone s.com> wrote in message
news:bq******** **@hercules.bti nternet.com... I am trying to setup a search facility, such that anything selected in any of 5 comboboxes is used as criteria unless null or "". I only want to get back results where ALL selections are in a table.
The tables are similar to this
1. UserID Name LName
2. ColourID ColourName
3. UserID ColourID
I can easily do it with an OR style - so that if I selected Blue Red I get anybody who has either of these colours in table 3. But I can't get it so that it returns UserID's from only the people who have all colours in the search.
Can anyone point me in the right direction? Haved searched, read FAQ's etc - even helping with keywords to continue searching would be
appreciated (is there a name for this type of search?)
Thanks in advance...
One approach would be:
SELECT User.UserID, User.Name, User.LName
FROM User
WHERE User.UserID IN
(SELECT UserID
FROM UserColour
GROUP BY UserID
HAVING Count(UserID) = 5)
(Replace 5 with however many colours there are in the Colour table)
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(No private e-mails, please)
"T_2k" <ma*****@tmjone s.com> wrote in message
news:bq******** **@hercules.bti nternet.com... I am trying to setup a search facility, such that anything selected in any of 5 comboboxes is used as criteria unless null or "". I only want to get back results where ALL selections are in a table.
The tables are similar to this
1. UserID Name LName
2. ColourID ColourName
3. UserID ColourID
I can easily do it with an OR style - so that if I selected Blue Red I get anybody who has either of these colours in table 3. But I can't get it so that it returns UserID's from only the people who have all colours in the search.
Can anyone point me in the right direction? Haved searched, read FAQ's etc - even helping with keywords to continue searching would be
appreciated (is there a name for this type of search?)
Thanks in advance...
"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:mu******** ************@ne ws04.bloor.is.n et.cable.rogers .com... One approach would be:
SELECT User.UserID, User.Name, User.LName FROM User WHERE User.UserID IN (SELECT UserID FROM UserColour GROUP BY UserID HAVING Count(UserID) = 5)
(Replace 5 with however many colours there are in the Colour table)
That wouldn't work. He doesn't want to see the Users having all Colors, he
wants to see the Users having at least the Colors he selects.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "T_2k" <ma*****@tmjone s.com> wrote in message news:bq******** **@hercules.bti nternet.com... I am trying to setup a search facility, such that anything selected in
any of 5 comboboxes is used as criteria unless null or "". I only want to
get back results where ALL selections are in a table.
The tables are similar to this
1. UserID Name LName
2. ColourID ColourName
3. UserID ColourID
I can easily do it with an OR style - so that if I selected Blue Red I
get anybody who has either of these colours in table 3. But I can't get it
so that it returns UserID's from only the people who have all colours in
the search.
Can anyone point me in the right direction? Haved searched, read FAQ's etc - even helping with keywords to continue searching would be appreciated (is there a name for this type of search?)
Thanks in advance...
I just realized something: in case your data in Table3 doesn't have at least
one entry for each color, the xtab query might cause an error if you search
for a color not in the data. You need to list out the ColorIDs in the
Crosstab query. Change the last line to:
PIVOT T2.ColorID in (1,2,3,4,5...)
Note: 1,2,3,4,5... are all the ColorIDs. When you add Colors to Table2,
you'll also need to manually update this xtab query.
"DFS" <no******@nospa m.com> wrote in message
news:vs******** ****@corp.super news.com... 1) Create a crosstab query to give you an alternate view of the data in Table 3
TRANSFORM First(T2.ColorN ame) AS Colors SELECT T1.UserID, T1.UserName FROM T2 INNER JOIN (T1 INNER JOIN T3 ON T1.UserID = T3.UserID) ON
T2.ColorID = T3.ColorID GROUP BY T1.UserID, T1.UserName PIVOT T2.Colorid;
Call this query Q_XTAB
Note: this query will show the UserID and Name as the Row Headings, the ColorIDs as the Column Headings, and the ColorName as the data
2) Bind your comboBoxes to the ColorID and hide it (if you want to - set width to 0"). If you don't want to put the ColorID in the comboBoxes, you'll need to change the query to show the ColorName as the
ColumnHeadings.
3) Build a dynamic query that processes each comboBox and executes against Q_XTAB
Public Sub btnSearchBoxes_ Click()
'CONFIRM AT LEAST ONE COMBO BOX IS SELECTED if isnull(me.combo 1) and isnull(me.combo 2) and isnull(me.combo 3) and isnull(me.combo 4) and isnull(me.combo 5) then msgbox "Choose at least one color" exit sub endif
'BUILD THE DYNAMIC QUERY dim cSQL as string cSQL = SELECT UserID, UserName cSQL = cSQL & "FROM Q_XTAB " cSQL = cSQL & "WHERE UserID IS NOT NULL AND " IF not IsNull(me.combo 1) then cSQL = cSQL & "AND " & me.combo1 & " IS NOT NULL " IF not IsNull(me.combo 2) then cSQL = cSQL & "AND " & me.combo2 & " IS NOT NULL " IF not IsNull(me.combo 3) then cSQL = cSQL & "AND " & me.combo3 & " IS NOT NULL " IF not IsNull(me.combo 4) then cSQL = cSQL & "AND " & me.combo4 & " IS NOT NULL " IF not IsNull(me.combo 5) then cSQL = cSQL & "AND " & me.combo5 & " IS NOT NULL "
End Sub
That should do it.
"T_2k" <ma*****@tmjone s.com> wrote in message news:bq******** **@hercules.bti nternet.com... I am trying to setup a search facility, such that anything selected in
any of 5 comboboxes is used as criteria unless null or "". I only want to
get back results where ALL selections are in a table.
The tables are similar to this
1. UserID Name LName
2. ColourID ColourName
3. UserID ColourID
I can easily do it with an OR style - so that if I selected Blue Red I
get anybody who has either of these colours in table 3. But I can't get it
so that it returns UserID's from only the people who have all colours in
the search.
Can anyone point me in the right direction? Haved searched, read FAQ's etc - even helping with keywords to continue searching would be appreciated (is there a name for this type of search?)
Thanks in advance...
Many, many thanks DFS (and Doug).
"DFS" <no******@nospa m.com> wrote in message
news:vs******** ****@corp.super news.com... I just realized something: in case your data in Table3 doesn't have at
least one entry for each color, the xtab query might cause an error if you
search for a color not in the data. You need to list out the ColorIDs in the Crosstab query. Change the last line to:
PIVOT T2.ColorID in (1,2,3,4,5...)
Note: 1,2,3,4,5... are all the ColorIDs. When you add Colors to Table2, you'll also need to manually update this xtab query.
"DFS" <no******@nospa m.com> wrote in message news:vs******** ****@corp.super news.com... 1) Create a crosstab query to give you an alternate view of the data in Table 3
TRANSFORM First(T2.ColorN ame) AS Colors SELECT T1.UserID, T1.UserName FROM T2 INNER JOIN (T1 INNER JOIN T3 ON T1.UserID = T3.UserID) ON T2.ColorID = T3.ColorID GROUP BY T1.UserID, T1.UserName PIVOT T2.Colorid;
Call this query Q_XTAB
Note: this query will show the UserID and Name as the Row Headings, the ColorIDs as the Column Headings, and the ColorName as the data
2) Bind your comboBoxes to the ColorID and hide it (if you want to - set width to 0"). If you don't want to put the ColorID in the comboBoxes, you'll need to change the query to show the ColorName as the ColumnHeadings.
3) Build a dynamic query that processes each comboBox and executes
against Q_XTAB
Public Sub btnSearchBoxes_ Click()
'CONFIRM AT LEAST ONE COMBO BOX IS SELECTED if isnull(me.combo 1) and isnull(me.combo 2) and isnull(me.combo 3) and isnull(me.combo 4) and isnull(me.combo 5) then msgbox "Choose at least one color" exit sub endif
'BUILD THE DYNAMIC QUERY dim cSQL as string cSQL = SELECT UserID, UserName cSQL = cSQL & "FROM Q_XTAB " cSQL = cSQL & "WHERE UserID IS NOT NULL AND " IF not IsNull(me.combo 1) then cSQL = cSQL & "AND " & me.combo1 & " IS
NOT NULL " IF not IsNull(me.combo 2) then cSQL = cSQL & "AND " & me.combo2 & " IS
NOT NULL " IF not IsNull(me.combo 3) then cSQL = cSQL & "AND " & me.combo3 & " IS
NOT NULL " IF not IsNull(me.combo 4) then cSQL = cSQL & "AND " & me.combo4 & " IS
NOT NULL " IF not IsNull(me.combo 5) then cSQL = cSQL & "AND " & me.combo5 & " IS
NOT NULL "
End Sub
That should do it.
"T_2k" <ma*****@tmjone s.com> wrote in message news:bq******** **@hercules.bti nternet.com... I am trying to setup a search facility, such that anything selected in any of 5 comboboxes is used as criteria unless null or "". I only want to get back results where ALL selections are in a table.
The tables are similar to this
1. UserID Name LName
2. ColourID ColourName
3. UserID ColourID
I can easily do it with an OR style - so that if I selected Blue Red I get anybody who has either of these colours in table 3. But I can't get
it so that it returns UserID's from only the people who have all colours in the search.
Can anyone point me in the right direction? Haved searched, read
FAQ's etc - even helping with keywords to continue searching would be
appreciated (is there a name for this type of search?)
Thanks in advance...
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: LouD |
last post by:
How can i include a combo box in the criteria field in a parameter query
|
by: meganrobertson22 |
last post by:
Hi Everyone-
I have a question about how to add and then use the "All" selection in
a combo box. I am trying to figure out how to:
(1) add "All" as a selection to a combo box and then
(2) how to use the selection "All" as criteria for a field in a
query, which is used to generate data for a report.
|
by: hmiller |
last post by:
Hey everyone,
I am having a hell of a time trying to set this menu system up. Here's
what I'm trying to do.
Combo Box One; is populated by names under properties "row source"
"Phase 1" through "Phase 10" (there are 10 Phases I want to sort from)
Once the phase has been selected a second combo box would populate.
|
by: MLH |
last post by:
I invoked the combo-box wizard today, telling it to use a 4-table
union query as a row-source for the combo-box it was assisting
me in building. The error I got was without number and stated,
"Data cannot be retrieved from the source you have selected.
You must select a different table or query to continue in the wizard."
just after I chose the query named
qryAuthsOwnersAddnlOwnersLienholders4OneCar.
Have any of you ever seen that error...
|
by: Tom |
last post by:
Hello,
I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what...
| |
by: deaconj999 |
last post by:
Hi,
I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!
Any kind soul out there that can give me an example of where to
start..........?
|
by: commodityintelligence |
last post by:
Greetings,
I am merging a series of different tables into one query to export decision-making information. I have some architecture issues I need to ask for help on. I have no programming training.
I will explain my structure before asking my questions.
I have data from a table (updated monthly by adding the most current month’s data) that is evaluated by certain arithmetic, given an adjective to describe it, and then being exported...
|
by: emily.a.day |
last post by:
I have a book database, and I have set up a query to search by
subject. As it is now, the searcher has to know the subject and type
it into the query. I wonder if there is a way to have a combo box
where the searcher can select the subject before running the query,
rather than consulting the separate subject list and typing the
subject in manually.
I have tried sticking the combo box in the report, but can't figure
that out. I think...
|
by: hyperpau |
last post by:
Hi there guys!
I have a Form where there are three comboboxes. This comboboxes are used as references for the parameter of 3 fields in a query. when I hit a command button in my form, it opens a query with the parameters selected from the form.
Let's say I have three fields in my query. ,, and
On the design view of the query, I put these criterias on different lines so that it would be using the OR:
=!!
|
by: Del |
last post by:
Hello and thanks for any and all assistance!
I have a database that is used by several users on several different
machines.
The backend database is housed on a file server.
Each user has a front end on their local machine.
The main form of the database has several query driven combo boxes.
These queries pull data from the back end database to populate the
combo boxes.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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...
|
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,...
|
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...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |