AND Query, 5 combo's

[T_2k]

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...

[DFS]

1) Create a crosstab query to give you an alternate view of the data in
Table 3

TRANSFORM First(T2.ColorName) 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.combo1) and isnull(me.combo2) and isnull(me.combo3) and
isnull(me.combo4) and isnull(me.combo5) 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.combo1) then cSQL = cSQL & "AND " & me.combo1 & " IS NOT
NULL "
IF not IsNull(me.combo2) then cSQL = cSQL & "AND " & me.combo2 & " IS NOT
NULL "
IF not IsNull(me.combo3) then cSQL = cSQL & "AND " & me.combo3 & " IS NOT
NULL "
IF not IsNull(me.combo4) then cSQL = cSQL & "AND " & me.combo4 & " IS NOT
NULL "
IF not IsNull(me.combo5) then cSQL = cSQL & "AND " & me.combo5 & " IS NOT
NULL "

End Sub
That should do it.


"T_2k" <ma*****@tmjones.com> wrote in message
news:bq**********@hercules.btinternet.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]

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*****@tmjones.com> wrote in message
news:bq**********@hercules.btinternet.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...

[DFS]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:mu********************@news04.bloor.is.net.ca ble.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*****@tmjones.com> wrote in message
news:bq**********@hercules.btinternet.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...

[DFS]

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******@nospam.com> wrote in message
news:vs************@corp.supernews.com...

1) Create a crosstab query to give you an alternate view of the data in
Table 3

TRANSFORM First(T2.ColorName) 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.combo1) and isnull(me.combo2) and isnull(me.combo3) and
isnull(me.combo4) and isnull(me.combo5) 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.combo1) then cSQL = cSQL & "AND " & me.combo1 & " IS NOT
NULL "
IF not IsNull(me.combo2) then cSQL = cSQL & "AND " & me.combo2 & " IS NOT
NULL "
IF not IsNull(me.combo3) then cSQL = cSQL & "AND " & me.combo3 & " IS NOT
NULL "
IF not IsNull(me.combo4) then cSQL = cSQL & "AND " & me.combo4 & " IS NOT
NULL "
IF not IsNull(me.combo5) then cSQL = cSQL & "AND " & me.combo5 & " IS NOT
NULL "

End Sub
That should do it.


"T_2k" <ma*****@tmjones.com> wrote in message
news:bq**********@hercules.btinternet.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...

[T_2k]

Many, many thanks DFS (and Doug).

"DFS" <no******@nospam.com> wrote in message
news:vs************@corp.supernews.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******@nospam.com> wrote in message
news:vs************@corp.supernews.com...

1) Create a crosstab query to give you an alternate view of the data in
Table 3

TRANSFORM First(T2.ColorName) 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.combo1) and isnull(me.combo2) and isnull(me.combo3) and
isnull(me.combo4) and isnull(me.combo5) 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.combo1) then cSQL = cSQL & "AND " & me.combo1 & " IS NOT NULL "
IF not IsNull(me.combo2) then cSQL = cSQL & "AND " & me.combo2 & " IS NOT NULL "
IF not IsNull(me.combo3) then cSQL = cSQL & "AND " & me.combo3 & " IS NOT NULL "
IF not IsNull(me.combo4) then cSQL = cSQL & "AND " & me.combo4 & " IS NOT NULL "
IF not IsNull(me.combo5) then cSQL = cSQL & "AND " & me.combo5 & " IS NOT NULL "

End Sub
That should do it.


"T_2k" <ma*****@tmjones.com> wrote in message
news:bq**********@hercules.btinternet.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...