Strange behavior on query - Help??

[Tim]

I have tblA with unique PrimaryKeyX.
Then tblB has many PrimaryKeyX matches, and for each there is a fieldY
that is either True or False.

tblB does not have a related match to every record in tblA, but every
record in tblB does relate to tblA by the PrimaryKeyX.

So, I need 2 queries. One that shows all records in tblA where the
related tblB records have ALL of their FieldY set to False, or where
there are no matches in tblB.
This works nice and quick as follows:

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

The other thing I need is: All records in tblA where the related
records in tblB where ONE OR MORE of the FieldY are set to True.

It was suggested to me that I achieve this by changing the above as
follows (changing FALSE to TRUE, and changing ALL to SOME):

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

HOWEVER... everytime I try and run this one, Access fails and wants me
to send an error report to Microsoft and repair my database.

I've tried creating an entirely new database, setting up fresh links
to my SQL server for tables, and importing the queries from old, but
the problem persists.

I would VERY much appreciate any help. All other solutions to getting
this query take too long to run.

 

[John Vinson]

The other thing I need is: All records in tblA where the related
records in tblB where ONE OR MORE of the FieldY are set to True.

It's much simpler than you're making it: since you're just checking
for the existance of any record, you can use a simple Join.

SELECT DISTINCT tblA.PrimaryKeyX
FROM tblA INNER JOIN tblB
ON tblA.PrimaryKeyX
WHERE tblB.fieldY=True;

The DISTINCT will cause the query to show only one record from tblA,
no matter how many occurances of FieldY are True; but if there are
none, there will be no match and therefore no retrieval.

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

 

[Tim]

This results in the error: "Join expression not supported". I've
looked at the meaning behind the error and tried playing with it but
can't figure it out.
Does that error make sense to you?
Thanks for the help!

 

[John Spencer (MVP)]

PARDON ME for jumping in.

John forgot the second part of the join in his sample SQL

SELECT DISTINCT TblA.PrimaryKeyX
FROM TblA INNER JOIN TblB

 

[John Vinson]

PARDON ME for jumping in.

John forgot the second part of the join in his sample SQL

SELECT DISTINCT TblA.PrimaryKeyX
FROM TblA INNER JOIN TblB
ON tblA.PrimaryKeyX = tblB.PrimaryKeyX
WHERE TblB.FieldY = True

Thanks John! Too hasty there; my apologies, Tim.

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

 

[Tim]

Thanks a lot guys!
That was painfully simple wasn't it .