How to retrieve values that match certain look-up values EXCLUSIVE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
This might be an easy problem for many of you, but my brain has gone
completely blank. Please help!
I have a table structured as follows:
Item Kit
==== ====
1000 A
1000 B
1000 C
2000 A
3000 A
3000 B
4000 C
I want to be able to query those Items that go on Kit A, B, or A & B "only".
It means if the Item goes on anything else besides A & B, I'm not interested
in it. So for this data set, I'd want to see Items 2000 & 3000 only because
Item 1000 violates the rule (it also goes on Kit C), and Item 4000 doesn't
even go on either A or B.
How do I achieve this?
Thanks much!
 
In the Criteria section of the query put

"A" and "B" or "A" or "B" under the Kit.
 
Hi,
Thanks for your response but your criteria wouldn't work because it will
return the following recordset:
1000 A
1000 B
2000 A
3000 A
3000 B
because Item 1000 does have 2 rows (or records) that satisfy your condition.
I DON'T want to see Item 1000 because it also goes on Kit C. I only want
those Items that go on either A or B (can be both) only.
Thanks!
ngan
 
I was able to obtain the results of those Items that match the criteria you
have indicated.

For example, it returns only the Item # that matches the query you want. To
do this I created three queries, the third using the second, the second using
the first, and the first taking from the original table.

I set up a table with two fields Item, and Index, where for example, Item =
1000, and Index = A.

qry1 SQL is:

SELECT tblHelp.Item, tblHelp.Index
FROM tblHelp
WHERE (((tblHelp.Index)="A" Or (tblHelp.Index)="B"));

qry2 SQL is:

SELECT tblHelp.Item, tblHelp.Index
FROM tblHelp LEFT JOIN qryHelp1 ON tblHelp.Index = qryHelp1.Index
WHERE (((qryHelp1.Index) Is Null));

qry3 SQL is:

SELECT DISTINCT [tblHelp].[Item], [qryHelp2].[Item]
FROM tblHelp LEFT JOIN qryHelp2 ON [tblHelp].[Item]=[qryHelp2].[Item]
WHERE ((([qryHelp2].[Item]) Is Null));

Now, you may be able to combine all three statements into one, if you know
how, I do not, and maybe someone could post that resultant SQL query that
combines all three, but the results of the final query is to provide only
2000 and 3000.

The logic is this. Provide all results that match the initial criteria
(either "A" or "B" or both "A" and "B" (Really testing for either gives this
result, not necessarily that both are true.)) Then set all results that do
not match the criteria to Qry2 giving Items and Indices. Then provide the
Items that are not listed in Qry2. This is results in all Items that do not
contain anything but "A" or "B". To test for all results that contain "A"
AND "B", you would have to look for all "A" then look for all "B" then if
both results contain the same Item then the result is "A" and "B"

Again, I do not know how to combine this as a single query vice three or
more separate queries.

However, this does provide the result you are looking for.
 
Thanks so much! That works out great! Thanks for the logic! :)
Also, as far as combining the queries, here's what I'd do if I wanted to
combine them (which is not any shorter than yours, but it'll be 1 query
instead):

SELECT DISTINCT [tblHelp].[Item], [qryHelp2].[Item]
FROM tblHelp LEFT JOIN
(
SELECT tblHelp.Item, tblHelp.Index
FROM tblHelp LEFT JOIN
(
SELECT tblHelp.Item, tblHelp.Index
FROM tblHelp
WHERE (((tblHelp.Index)="A" Or (tblHelp.Index)="B"))
) AS qryHelp1
ON tblHelp.Index = qryHelp1.Index
WHERE (((qryHelp1.Index) Is Null))
) AS qryHelp2
ON [tblHelp].[Item]=[qryHelp2].[Item]
WHERE ((([qryHelp2].[Item]) Is Null));

Again thank you very much!
 
And thank you, I haven't seen this format of a query anywhere in my ventures
of manual/help book review. I have always had to construct the sequential
queries as I provided them to you, and try to remember/name them so that I
would know what they were returning.

I think the format provided (and I do not think that the query can be
written any shorter, it's that it will exist as a single query vice multiple
queries in the query view) will be highly valuable in my continued attempts
to properly create and utilize a database.

I'm mostly self taught with Access, but have a thorough background on many
other programming languages and program usage.

Again thank you as well.
 
I'm glad I can offer something in return as well! Without going into any
details, here's how basic nested queries work (or I think that's how they
call it ;-)

SELECT Query1.*, Query2.* FROM
( Another SELECT statement ) AS Query1 INNER JOIN [or LEFT JOIN, or RIGHT
JOIN whatever]
( Another SELECT statement ) AS Query2
ON Whatever

I believe you can look up "Nested Queries" in your Access books and will
certainly find topics on them. That's how I always type into Access SQL View
but after you save the queries, Access will change the syntax (usually from
parentheses to square brackets) (I don't know why and maybe someone here can
explain that). For EX, I just created this nested query:

SELECT Query1.Index, Query1.Name, Query1.Grade
FROM
(
SELECT Names.Index, Names.Name, Grades.Grade
FROM Grades INNER JOIN Names ON Grades.Index = Names.Index
) AS Query1
WHERE Query1.Grade="A";

After I save it, this is what Access change it to:

SELECT Query1.Index, Query1.Name, Query1.Grade
FROM [SELECT Names.Index, Names.Name, Grades.Grade
FROM Grades INNER JOIN Names ON Grades.Index = Names.Index
]. AS Query1
WHERE Query1.Grade="A";

Like you, I learn Access on my own and these discussion boards have been a
tremendous help :-) Thanks much again!!!
 
Back
Top