Re: Multiple AND criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karla V" <KarlaV@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ADA10DE5-1E54-469B-A84B-5C8909D0075E@xxxxxxxxxxxxxxxx
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...



.

Relevant Pages

  • Re: A query based on several other queries when one of the other quer.
    ... And then you run the query for information on a customer who ... If you know which of the subqueries is without records, ... > I have a query based on several other sub-queries. ...
    (microsoft.public.access.queries)
  • RE: Help with creative thinking needed
    ... Also I note that it doesn't handle subqueries. ... Another specific name that comes to mind for query performance is Joe ... designated stock items and have a field of # of Days to Stock. ... Are there other ways of shaving milliseconds from calculations and queries, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Crosstab query will work one way but not the other
    ... First, no offense taken. ... Second, my original setup was a cross tab query based on 3 subqueries, somewhat like you mentioned at the bottom of your reply. ... the Jet query parser. ...
    (microsoft.public.access.queries)
  • Re: Update Query Overwrite?
    ... >> I apologize....because your subqueries contain Sum functions in them, ... >> query, and such a query loses its uniqueness). ... >> <MS ACCESS MVP> ... >>> In the table tblBranchStats I have DateRec As Date/Time and the other ...
    (microsoft.public.access.queries)
  • RE: Replace Parentheses with Bracket in Subquery
    ... Prevent it by not using subqueries in that way. ... queries and use those queries as the input "tables" to the main query. ... INNER JOIN ... FROM HardwareSales GROUP BY EmpNo) AS H ...
    (microsoft.public.access.queries)