Strange behavior on query - Help??

From: Tim (tmiller_at_deppmann.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 12:28:16 -0500

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.

Relevant Pages

  • Re: Update all records in VBA
    ... there's still some unique ID that is consistant in tblA and tblB after the ... are then stored in tblParts. ... doing the import and update tblA and tblB in VBA. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Trigger After Delete - I need a Trigger Before Delete
    ... I have a table tblB which has to be filled whenever in table tblA ... This view vwC is based on a key field used in tblA. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • RE: SQL question - compare 2 tables
    ... To find which entries are in tblA but not in tblB, ... > of the parts in tblB may be missing. ...
    (microsoft.public.access.queries)
  • Re: Option Group
    ... Yes they have the same feilds and since thay have the ... same feilds on bother tblA anf tblB I have decided to may ... Option Group: ...
    (microsoft.public.access.forms)
  • Re: Strange behavior on query - Help??
    ... >records in tblB where ONE OR MORE of the FieldY are set to True. ... FROM tblA INNER JOIN tblB ... no matter how many occurances of FieldY are True; ...
    (microsoft.public.access.queries)