Multiple AND criteria

[Guest]

I'm helping someone with a parts database. One table has the part number and
a generic description such as spring, bolt, etc. A second table has a list
of attributes for that part. For example, a single bolt might have 3
attributes [attribute]...one for size, one for length, one for type. Each
attribute also has a corresponding value [value]. Each part can have
multiple related attribute/value records.

The problem is in trying to query for multiple criteria. For example, I
need to seach for any bolt that is:

[attribute]=length and [value]=3
AND
[attribute]=size and [value]=small

It's possible that some parts may have up to 10 different attributes/value
combinations.

How can this be done? I know I could query from a query, but the number of
attributes will change often.

Thanks in advance.....

 

[Allen Browne]

For the first condition, you can use an INNER JOIN.
After that, use a subquery for each subsequent condition.

Example:

SELECT tblPart.*
FROM tblPart INNER JOIN tblPartAttrib
ON tblPartAttrib.PartID = tblPart.PartID
WHERE (tblPartAttrib.[attrib]='length') AND (tblPartAttrib.[value] = '3')
AND EXISTS
(SELECT AttribID FROM tblPartAttrib AS Dupe
WHERE Dupe.PartID = tblPart.PartID
AND Dupe.[attrib]='size' AND Dupe.[value] = 'small')
AND EXISTS
(SELECT ...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you just want to filter a form or report, you can use these subqueries in
the Filter string, or in the WhereCondition of OpenReport.

Alternatively, you can build the entire SQL string in VBA, and assign it to
the RecordSource of your form/report, or the SQL property of an existing
QueryDef.

 

[John Spencer]

Another alternative that might work

Assumption:
tblPartAttrib only has one record where the partID, attribute, and value are
the same. (Unique index on the combination of the three fields)

SELECT tblPart.*
FROM TblPart
WHERE tblPart.PartId in
(SELECT tblPartAttrib.PartID
FROM tblPartAttrib
WHERE tblPartAttrib= "length" and tblPart.Value = "3"
Or tlbPartAttrib="Size" and tblPart.Value="small"
Or tblPartAttib="Color" and tblPart.Value="Brown"
GROUP BY PartID
HAVING Count(PartID) = 3)

For the first condition, you can use an INNER JOIN.
After that, use a subquery for each subsequent condition.

Example:

SELECT tblPart.*
FROM tblPart INNER JOIN tblPartAttrib
ON tblPartAttrib.PartID = tblPart.PartID
WHERE (tblPartAttrib.[attrib]='length') AND (tblPartAttrib.[value] = '3')
AND EXISTS
(SELECT AttribID FROM tblPartAttrib AS Dupe
WHERE Dupe.PartID = tblPart.PartID
AND Dupe.[attrib]='size' AND Dupe.[value] = 'small')
AND EXISTS
(SELECT ...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you just want to filter a form or report, you can use these subqueries
in the Filter string, or in the WhereCondition of OpenReport.

Alternatively, you can build the entire SQL string in VBA, and assign it
to the RecordSource of your form/report, or the SQL property of an
existing QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm helping someone with a parts database. One table has the part number
and
a generic description such as spring, bolt, etc. A second table has a
list
of attributes for that part. For example, a single bolt might have 3
attributes [attribute]...one for size, one for length, one for type.
Each
attribute also has a corresponding value [value]. Each part can have
multiple related attribute/value records.

The problem is in trying to query for multiple criteria. For example, I
need to seach for any bolt that is:

[attribute]=length and [value]=3
AND
[attribute]=size and [value]=small

It's possible that some parts may have up to 10 different
attributes/value
combinations.

How can this be done? I know I could query from a query, but the number
of
attributes will change often.

Thanks in advance...

 

[Allen Browne]

Great suggestion, John.
Very efficient approach (assuming duplicates are not possible.)

Might be good to add some brackets on those mixed ANDs and ORs in the
subquery:

WHERE (tblPartAttrib= "length" and tblPart.Value = "3")
Or (tlbPartAttrib="Size" and tblPart.Value="small")
Or (tblPartAttib="Color" and tblPart.Value="Brown")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Another alternative that might work

Assumption:
tblPartAttrib only has one record where the partID, attribute, and value
are the same. (Unique index on the combination of the three fields)

SELECT tblPart.*
FROM TblPart
WHERE tblPart.PartId in
(SELECT tblPartAttrib.PartID
FROM tblPartAttrib
WHERE tblPartAttrib= "length" and tblPart.Value = "3"
Or tlbPartAttrib="Size" and tblPart.Value="small"
Or tblPartAttib="Color" and tblPart.Value="Brown"
GROUP BY PartID
HAVING Count(PartID) = 3)

For the first condition, you can use an INNER JOIN.
After that, use a subquery for each subsequent condition.

Example:

SELECT tblPart.*
FROM tblPart INNER JOIN tblPartAttrib
ON tblPartAttrib.PartID = tblPart.PartID
WHERE (tblPartAttrib.[attrib]='length') AND (tblPartAttrib.[value] = '3')
AND EXISTS
(SELECT AttribID FROM tblPartAttrib AS Dupe
WHERE Dupe.PartID = tblPart.PartID
AND Dupe.[attrib]='size' AND Dupe.[value] = 'small')
AND EXISTS
(SELECT ...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you just want to filter a form or report, you can use these subqueries
in the Filter string, or in the WhereCondition of OpenReport.

Alternatively, you can build the entire SQL string in VBA, and assign it
to the RecordSource of your form/report, or the SQL property of an
existing QueryDef.

I'm helping someone with a parts database. One table has the part
number and
a generic description such as spring, bolt, etc. A second table has a
list
of attributes for that part. For example, a single bolt might have 3
attributes [attribute]...one for size, one for length, one for type.
Each
attribute also has a corresponding value [value]. Each part can have
multiple related attribute/value records.

The problem is in trying to query for multiple criteria. For example, I
need to seach for any bolt that is:

[attribute]=length and [value]=3
AND
[attribute]=size and [value]=small

It's possible that some parts may have up to 10 different
attributes/value
combinations.

How can this be done? I know I could query from a query, but the number
of
attributes will change often.

Thanks in advance...

 

[Guest]

Thanks for the information. Sub-queries are new to me, so I've read the
information at the link you provided. Unfortunately, I am not a coder,
although I can usually make some sense of code that's already been written.

So, given the above I have a few questions:

I am unclear on where exactly I'm typing the code. Do I enter the first set
of criteria in the 1st row? Then, the "subquery" code in line 2? Which
column should contain the subquery since I have criteria on multiple fields.
Or, is everything written in SQL view? What is the significance of "dupe"?
Do the parenthesis designate the sub-query?

Sorry if these questions seem basic. I think this is very valuable
information I can use in many other databases so I'm anxious to learn how to
do it correctly.

Thank you again in advance....

For the first condition, you can use an INNER JOIN.
After that, use a subquery for each subsequent condition.

Example:

SELECT tblPart.*
FROM tblPart INNER JOIN tblPartAttrib
ON tblPartAttrib.PartID = tblPart.PartID
WHERE (tblPartAttrib.[attrib]='length') AND (tblPartAttrib.[value] = '3')
AND EXISTS
(SELECT AttribID FROM tblPartAttrib AS Dupe
WHERE Dupe.PartID = tblPart.PartID
AND Dupe.[attrib]='size' AND Dupe.[value] = 'small')
AND EXISTS
(SELECT ...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you just want to filter a form or report, you can use these subqueries in
the Filter string, or in the WhereCondition of OpenReport.

Alternatively, you can build the entire SQL string in VBA, and assign it to
the RecordSource of your form/report, or the SQL property of an existing
QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm helping someone with a parts database. One table has the part number
and
a generic description such as spring, bolt, etc. A second table has a
list
of attributes for that part. For example, a single bolt might have 3
attributes [attribute]...one for size, one for length, one for type. Each
attribute also has a corresponding value [value]. Each part can have
multiple related attribute/value records.

The problem is in trying to query for multiple criteria. For example, I
need to seach for any bolt that is:

[attribute]=length and [value]=3
AND
[attribute]=size and [value]=small

It's possible that some parts may have up to 10 different attributes/value
combinations.

How can this be done? I know I could query from a query, but the number
of
attributes will change often.

Thanks in advance...

 

[John Spencer]

Yeah. It might be a REALLY, REALLY good idea if you want to get anything
approaching a valid result.

Great suggestion, John.
Very efficient approach (assuming duplicates are not possible.)

Might be good to add some brackets on those mixed ANDs and ORs in the
subquery:

WHERE (tblPartAttrib= "length" and tblPart.Value = "3")
Or (tlbPartAttrib="Size" and tblPart.Value="small")
Or (tblPartAttib="Color" and tblPart.Value="Brown")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Another alternative that might work

Assumption:
tblPartAttrib only has one record where the partID, attribute, and value
are the same. (Unique index on the combination of the three fields)

SELECT tblPart.*
FROM TblPart
WHERE tblPart.PartId in
(SELECT tblPartAttrib.PartID
FROM tblPartAttrib
WHERE tblPartAttrib= "length" and tblPart.Value = "3"
Or tlbPartAttrib="Size" and tblPart.Value="small"
Or tblPartAttib="Color" and tblPart.Value="Brown"
GROUP BY PartID
HAVING Count(PartID) = 3)

For the first condition, you can use an INNER JOIN.
After that, use a subquery for each subsequent condition.

Example:

SELECT tblPart.*
FROM tblPart INNER JOIN tblPartAttrib
ON tblPartAttrib.PartID = tblPart.PartID
WHERE (tblPartAttrib.[attrib]='length') AND (tblPartAttrib.[value] =
'3')
AND EXISTS
(SELECT AttribID FROM tblPartAttrib AS Dupe
WHERE Dupe.PartID = tblPart.PartID
AND Dupe.[attrib]='size' AND Dupe.[value] = 'small')
AND EXISTS
(SELECT ...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you just want to filter a form or report, you can use these
subqueries in the Filter string, or in the WhereCondition of OpenReport.

Alternatively, you can build the entire SQL string in VBA, and assign it
to the RecordSource of your form/report, or the SQL property of an
existing QueryDef.

I'm helping someone with a parts database. One table has the part
number and
a generic description such as spring, bolt, etc. A second table has a
list
of attributes for that part. For example, a single bolt might have 3
attributes [attribute]...one for size, one for length, one for type.
Each
attribute also has a corresponding value [value]. Each part can have
multiple related attribute/value records.

The problem is in trying to query for multiple criteria. For example,
I
need to seach for any bolt that is:

[attribute]=length and [value]=3
AND
[attribute]=size and [value]=small

It's possible that some parts may have up to 10 different
attributes/value
combinations.

How can this be done? I know I could query from a query, but the
number of
attributes will change often.

Thanks in advance...

 

[Allen Browne]

The queries that John and I suggested are designed to go into SQL View of a
query.

A subquery is like a whole SELECT query inside another query. And yes, you
must put brackets around it.

To build a subquery, you can mock up a query. Then switch it to SQL view to
see what it should look like. Ultimately, you type the subquery into the
Field row of a query.

If you are familiar with VBA code, you could just take the WHERE clause of
the main query statement and apply it as the Filter proeprty of your form.
Or you can build the entire SQL statement and assign it to the RecordSource
of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the information. Sub-queries are new to me, so I've read the
information at the link you provided. Unfortunately, I am not a coder,
although I can usually make some sense of code that's already been
written.

So, given the above I have a few questions:

I am unclear on where exactly I'm typing the code. Do I enter the first
set
of criteria in the 1st row? Then, the "subquery" code in line 2? Which
column should contain the subquery since I have criteria on multiple
fields.
Or, is everything written in SQL view? What is the significance of
"dupe"?
Do the parenthesis designate the sub-query?

Sorry if these questions seem basic. I think this is very valuable
information I can use in many other databases so I'm anxious to learn how
to
do it correctly.

Thank you again in advance....

For the first condition, you can use an INNER JOIN.
After that, use a subquery for each subsequent condition.

Example:

SELECT tblPart.*
FROM tblPart INNER JOIN tblPartAttrib
ON tblPartAttrib.PartID = tblPart.PartID
WHERE (tblPartAttrib.[attrib]='length') AND (tblPartAttrib.[value] = '3')
AND EXISTS
(SELECT AttribID FROM tblPartAttrib AS Dupe
WHERE Dupe.PartID = tblPart.PartID
AND Dupe.[attrib]='size' AND Dupe.[value] = 'small')
AND EXISTS
(SELECT ...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you just want to filter a form or report, you can use these subqueries
in
the Filter string, or in the WhereCondition of OpenReport.

Alternatively, you can build the entire SQL string in VBA, and assign it
to
the RecordSource of your form/report, or the SQL property of an existing
QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm helping someone with a parts database. One table has the part
number
and
a generic description such as spring, bolt, etc. A second table has a
list
of attributes for that part. For example, a single bolt might have 3
attributes [attribute]...one for size, one for length, one for type.
Each
attribute also has a corresponding value [value]. Each part can have
multiple related attribute/value records.

The problem is in trying to query for multiple criteria. For example,
I
need to seach for any bolt that is:

[attribute]=length and [value]=3
AND
[attribute]=size and [value]=small

It's possible that some parts may have up to 10 different
attributes/value
combinations.

How can this be done? I know I could query from a query, but the
number
of
attributes will change often.

Thanks in advance...