Re: Multiple AND criteria
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sat, 24 Jun 2006 12:13:35 +0800
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.
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:60C0CF00-A928-4829-96C3-CB80994A8470@xxxxxxxxxxxxxxxx
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...
.
- References:
- Re: Multiple AND criteria
- From: Allen Browne
- Re: Multiple AND criteria
- From: Karla V
- Re: Multiple AND criteria
- Prev by Date: RE: Is the INSERT INTO Query best to use to Add To (not just appen
- Next by Date: Re: Format of Calculated Field in Query
- Previous by thread: Re: Multiple AND criteria
- Next by thread: Re: Prompts and/or reminders?
- Index(es):
Relevant Pages
|
