PC Review


Reply
Thread Tools Rate Thread

Strange behavior on query - Help??

 
 
Tim
Guest
Posts: n/a
 
      13th Dec 2004
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.
 
Reply With Quote
 
 
 
 
John Vinson
Guest
Posts: n/a
 
      13th Dec 2004
On Mon, 13 Dec 2004 12:28:16 -0500, Tim <(E-Mail Removed)> wrote:

>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
 
Reply With Quote
 
 
 
 
Tim
Guest
Posts: n/a
 
      13th Dec 2004
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!

On Mon, 13 Dec 2004 13:43:23 -0700, John Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

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



 
Reply With Quote
 
John Spencer (MVP)
Guest
Posts: n/a
 
      14th Dec 2004
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

Tim wrote:
>
> 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!
>
> On Mon, 13 Dec 2004 13:43:23 -0700, John Vinson
> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>
> >SELECT DISTINCT tblA.PrimaryKeyX
> >FROM tblA INNER JOIN tblB
> >ON tblA.PrimaryKeyX
> >WHERE tblB.fieldY=True;

 
Reply With Quote
 
John Vinson
Guest
Posts: n/a
 
      14th Dec 2004
On Mon, 13 Dec 2004 20:12:31 -0500, "John Spencer (MVP)"
<(E-Mail Removed)> wrote:

>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
 
Reply With Quote
 
Tim
Guest
Posts: n/a
 
      14th Dec 2004
Thanks a lot guys!
That was painfully simple wasn't it .

On Mon, 13 Dec 2004 20:12:31 -0500, "John Spencer (MVP)"
<(E-Mail Removed)> wrote:

>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
>
>Tim wrote:
>>
>> 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!
>>
>> On Mon, 13 Dec 2004 13:43:23 -0700, John Vinson
>> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>>
>> >SELECT DISTINCT tblA.PrimaryKeyX
>> >FROM tblA INNER JOIN tblB
>> >ON tblA.PrimaryKeyX
>> >WHERE tblB.fieldY=True;


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help: Strange, Strange Behavior ( Not Me. My Model! ) SteveM Microsoft Excel Programming 2 16th Dec 2007 07:02 PM
Strange behavior when closing query ag Microsoft Access Queries 1 20th Aug 2004 02:57 AM
Strange Query Behavior (Repost) Dale Fye Microsoft Access Form Coding 6 18th May 2004 02:58 AM
Strange Query Behavior (Repost) Dale Fye Microsoft Access Queries 4 17th May 2004 08:34 PM
Strange query behavior Dale Fye Microsoft Access Queries 0 14th May 2004 05:42 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:12 AM.