Simple one to many question

[Tim]

I feel like I've done this before but I just can't get my brain
working...

Table A has unique PrimaryKeyX
Table B has many PrimaryKeyX, and also FieldY which is True/False.

What I want to do here is run a query that gives me ONE result of
TableA for any instance where ALL of the related records in TableB,
fieldY are False.
If even one of the related records in Table B, fieldY are True, then I
don't want to see the Table A record.

I feel like this can be accomplished without 2 or 3 queries, but at
this point, I'll take any answer.

Thanks very much for any help!

Tim

 

[Guest]

You should be able to achieve this in one query along the lines of

SELECT T1.*
FROM TableA AS T1
INNER JOIN
(SELECT primaryKeyX FROM TableB GROUP BY primaryKeyX HAVING
Sum(IIF(FieldY,1,0)) = 0) AS Q1
ON T1.primaryKeyX = Q1.primaryKeyX

Hope This Helps
Gerald Stanley MCSD

 

[Marshall Barton]

I feel like I've done this before but I just can't get my brain
working...

Table A has unique PrimaryKeyX
Table B has many PrimaryKeyX, and also FieldY which is True/False.

What I want to do here is run a query that gives me ONE result of
TableA for any instance where ALL of the related records in TableB,
fieldY are False.
If even one of the related records in Table B, fieldY are True, then I
don't want to see the Table A record.

This should do what you want:


SELECT tblA.PrimaryKeyX
FROM tblA
WHERE False = ALL (SELECT tblB.fieldY
FROM tblB
WHERE tblA.PrimaryKeyX= tblB.PrimaryKeyX)

 

[Tim]

I failed to mention here that in some cases table B has no PrimaryKeyX
matches.
Your solution here works nice and fast (the other posted response
worked well, but was slow).

It also results in all records where there is no match in TableB, and
that is good.

HOWEVER, could you explain how I could, using this solution, show
results only where one or more of the matches in TableB are TRUE, and
additionally how I can limit it to only those records that contain a
matching PrimaryKeyX in TableB.

I tried playing with yours, changing the False, change =, but only
thing I got was results where ALL are True, which is rare.

Thanks a LOT for the help!

 

[Marshall Barton]

You missed the critical change ;-)

SELECT tblA.PrimaryKeyX
FROM tblA
WHERE True = SOME (SELECT tblB.fieldY
FROM tblB
WHERE tblA.PrimaryKeyX= tblB.PrimaryKeyX)

You know, if all you want id the PKey field, there's no
reason to use a subquery. You could just get it from tblB
using a simple WHERE clause.

 

[Tim]

Everytime I try to run this, Access closes. I've tried just copying
the other query and changing the words as below, and tried totally
recreating it based on below. I tried several different times,
including after a reboot and after compacting and repairing the mdb
file. Everytime, Access closes and asks me to report the error to MS.
So then I looked in help and tried DISTINCT and DISTINCTROW, both of
which just didn't work.

Any thoughts? I appreciate the help.

 

[John Vinson]

Everytime I try to run this, Access closes.

Then it's not a problem with the Query - it's either a corrupt
database of a damaged installation of Access. AFAIK there is NO way to
change the SQL of a query (other than to have it call a VBA function)
to make it close Access!

Try creating a new, empty database and import everything from your
database into it. Check out Tony's CorruptionFAQ at

http://www.granite.ab.ca/access/corruptmdbs.htm

for further suggestions and diagnoses.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps